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

搭建环境

构建一个表
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(*)
----------
         1
SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;
  COUNT(*)
----------
         1
SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;
  COUNT(*)
----------
         1
SQL> 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            0
SQL> 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=:a

Plan 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 |
---------