直方图；绑定变量窥探；自适应游标

搭建环境

构建一个表CREATE TABLE TEST2 AS SELECT * FROM DBA_OBJECTS;UPDATE SET OBJECT_ID = 11111 WHERE ROWNUM <=50000;建立索引：CREATE INDEX IND_TEST2_OBJECT_ID ON TEST2(OBJECT_ID);收集统计信息，不收集直方图EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'TEST2',ESTIMATE_PERCENT=>100,cascade=>true,method_opt => 'for all columns size 1');

SQL> SELECT OBJECT_ID FROM TEST2 WHERE OBJECT_NAME = 'TEST'; OBJECT_ID----------     52694

毋庸置疑index range scan
SELECT count(*) FROM TEST2 WHERE OBJECT_ID=11111;
SELECT count(*) FROM TEST2 WHERE OBJECT_ID=52694;

收集直方图

BEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',                                tabname => 'TEST2',                                estimate_percent => 100,                                method_opt => 'for all columns size skewonly',                                no_invalidate => FALSE,                                degree => 1,                                cascade => TRUE);END;/

全表扫描
SELECT count(*) FROM TEST2 WHERE OBJECT_ID=11111;
index range scan
SELECT count(*) FROM TEST2 WHERE OBJECT_ID=52694;

但当直方图碰上绑定变量：
9i，10g

alter system flush shared_pool;var a number;exec :a := 52694;SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;  COUNT(*)----------         1SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;  COUNT(*)----------         1SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;  COUNT(*)----------         1SQL> select sql_id,child_number from v$sql where sql_text like 'SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a%';SQL_ID        CHILD_NUMBER------------- ------------22gd2utvtttmj            0SQL> select * from table(dbms_xplan.display_cursor('22gd2utvtttmj',0,'advanced peeked_binds'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  22gd2utvtttmj, child number 0-------------------------------------SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:aPlan hash value: 3600962442-----------------------------------------------------------------------------------------| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |                     |       |       |     1 (100)|          ||   1 |  SORT AGGREGATE   |                     |     1 |     5 |            |          ||*  2 |   INDEX RANGE SCAN| IND_TEST2_OBJECT_ID |     1 |     5 |     1   (0)| 00:00:01 |-----------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1   2 - SEL$1 / TEST2@SEL$1Outline Data-------------  /*+      BEGIN_OUTLINE_DATA      IGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')      ALL_ROWS      OUTLINE_LEAF(@"SEL$1")      INDEX(@"SEL$1" "TEST2"@"SEL$1" ("TEST2"."OBJECT_ID"))      END_OUTLINE_DATA  */Peeked Binds (identified by position):--------------------------------------   1 - :A (NUMBER): 52694Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_ID"=:A)Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - (#keys=0) COUNT(*)[22]==================================================================================================================

exec :a := 11111;SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;  COUNT(*)----------     50000SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;  COUNT(*)----------     50000SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;  COUNT(*)----------     50000SQL> select sql_id,child_number from v$sql where sql_text like 'SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a%';SQL_ID        CHILD_NUMBER------------- ------------22gd2utvtttmj            0SQL>  select * from table(dbms_xplan.display_cursor('22gd2utvtttmj',0,'advanced peeked_binds'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  22gd2utvtttmj, child number 0-------------------------------------SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:aPlan hash value: 3600962442-----------------------------------------------------------------------------------------| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |                     |       |       |     1 (100)|          ||   1 |  SORT AGGREGATE   |                     |     1 |     5 |            |          ||*  2 |   INDEX RANGE SCAN| IND_TEST2_OBJECT_ID |     1 |     5 |     1   (0)| 00:00:01 |-----------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1   2 - SEL$1 / TEST2@SEL$1Outline Data-------------  /*+      BEGIN_OUTLINE_DATA      IGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')      ALL_ROWS      OUTLINE_LEAF(@"SEL$1")      INDEX(@"SEL$1" "TEST2"@"SEL$1" ("TEST2"."OBJECT_ID"))      END_OUTLINE_DATA  */Peeked Binds (identified by position):--------------------------------------   1 - :A (NUMBER): 52694Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_ID"=:A)Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - (#keys=0) COUNT(*)[22]

从上面可以发现，由于绑定变量只有在硬解析的时候才回去“窥探”，所以硬解析时候窥探的绑定变量的值就决定了整个执行计划，即使发生改变oracle的也不管。这个就比较坑爹了。
11g

SQL> var a number;SQL> exec :a := 52694;PL/SQL procedure successfully completed.SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;  COUNT(*)----------         1SQL>  SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;  COUNT(*)----------         1SQL>  SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;  COUNT(*)----------         1SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced peeked_binds'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  67wpp1xx1jsb4, child number 0------------------------------------- SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:aPlan hash value: 3600962442-----------------------------------------------------------------------------------------| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |                     |       |       |     1 (100)|          ||   1 |  SORT AGGREGATE   |                     |     1 |     5 |            |          ||*  2 |   INDEX RANGE SCAN| IND_TEST2_OBJECT_ID |     1 |     5 |     1   (0)| 00:00:01 |-----------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1   2 - SEL$1 / TEST2@SEL$1Outline Data-------------  /*+      BEGIN_OUTLINE_DATA      IGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')      DB_VERSION('11.2.0.3')      ALL_ROWS      OUTLINE_LEAF(@"SEL$1")      INDEX(@"SEL$1" "TEST2"@"SEL$1" ("TEST2"."OBJECT_ID"))      END_OUTLINE_DATA  */Peeked Binds (identified by position):--------------------------------------   1 - :A (NUMBER): 52694Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_ID"=:A)Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - (#keys=0) COUNT(*)[22]==============================================================================SQL> exec :a := 11111;PL/SQL procedure successfully completed.SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;  COUNT(*)----------     50000SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;  COUNT(*)----------     50000SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;  COUNT(*)----------     50000SQL> select sql_id,child_number from v$sql where sql_text like 'SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a%';SQL_ID        CHILD_NUMBER------------- ------------22gd2utvtttmj            022gd2utvtttmj            122gd2utvtttmj            2SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;  COUNT(*)----------     50000SQL>  select * from table(dbms_xplan.display_cursor(null,null,'advanced peeked_binds'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  22gd2utvtttmj, child number 2-------------------------------------SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:aPlan hash value: 1970853342---------------------------------------------------------------------------------------------| Id  | Operation             | Name                | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |                     |       |       |    48 (100)|          ||   1 |  SORT AGGREGATE       |                     |     1 |     5 |            |          ||*  2 |   INDEX FAST FULL SCAN| IND_TEST2_OBJECT_ID | 49956 |   243K|    48   (3)| 00:00:01 |---------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1   2 - SEL$1 / TEST2@SEL$1Outline Data-------------  /*+      BEGIN_OUTLINE_DATA      IGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')      DB_VERSION('11.2.0.3')      ALL_ROWS      OUTLINE_LEAF(@"SEL$1")      INDEX_FFS(@"SEL$1" "TEST2"@"SEL$1" ("TEST2"."OBJECT_ID"))      END_OUTLINE_DATA  */Peeked Binds (identified by position):--------------------------------------   1 - :A (NUMBER): 11111Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("OBJECT_ID"=:A)Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - (#keys=0) COUNT(*)[22]49 rows selected.SQL>  exec :a := 52694;PL/SQL procedure successfully completed.SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;  COUNT(*)----------         1SQL>  select * from table(dbms_xplan.display_cursor(null,null,'advanced peeked_binds'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  22gd2utvtttmj, child number 1-------------------------------------SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:aPlan hash value: 3600962442-----------------------------------------------------------------------------------------| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |                     |       |       |     1 (100)|          ||   1 |  SORT AGGREGATE   |                     |     1 |     5 |            |          ||*  2 |   INDEX RANGE SCAN| IND_TEST2_OBJECT_ID |     1 |     5 |     1   (0)| 00:00:01 |-----------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1   2 - SEL$1 / TEST2@SEL$1Outline Data-------------  /*+      BEGIN_OUTLINE_DATA      IGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')      DB_VERSION('11.2.0.3')      ALL_ROWS      OUTLINE_LEAF(@"SEL$1")      INDEX(@"SEL$1" "TEST2"@"SEL$1" ("TEST2"."OBJECT_ID"))      END_OUTLINE_DATA  */Peeked Binds (identified by position):--------------------------------------   1 - :A (NUMBER): 75819Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_ID"=:A)Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - (#keys=0) COUNT(*)[22]

从上面可以看出来，11g之后，oracle引入自适应游标，oracle会根据绑定变量值得改变而改变执行计划了。这才是我们需要的。