--使用绑定窥探，先使用查小部分数据的条件
SQL> alter system flush shared_pool; SQL> alter system flush buffer_cache; SQL> select hash_value, child_number from v$sql where sql_text = 'select * from test where name=:ccc'; 未选定行 SQL>exec :ccc:='200'; SQL> set autotrace trace stat SQL> select * from test where name=:ccc; 统计信息 ---------------------------------------------------------- 354 recursive calls 0 db block gets 54 consistent gets 27 physical reads 0 redo size 1342 bytes sent via SQL*Net to client 338 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 1 rows processed SQL> set autotrace off SQL> select hash_value, child_number from v$sql where sql_text = 'select * from test where name=:ccc'; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 0 SQL> select * from table(dbms_xplan.display_cursor(1301684711,0)); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ HASH_VALUE 1301684711, child number 0 -------------------------------------- select * from test where name=:ccc Plan hash value: 688048857 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 909 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_NAME | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NAME"=:CCC) 第一次执行ccc:='100' SQL> exec :ccc:='100';