| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 11 | 2 (0)| 00
|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
19 rows selected
第二次执行相同SQL，我们可以看到生成的shared cursor进行共享。
SQL> select sql_id, executions, version_count, first_load_time from v$sqlarea where sql_text like 'select /*+demo*/%';
SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME
------------- ---------- ------------- ----------------------------------------------------------------------------
cnb0ktgvms6vq 2 1 2014-01-06/00:04:29
修改数据object_id取值，改变数据分布。
SQL> update t set object_id=1000;
72729 rows updated
SQL> commit;
Commit complete
第三次执行。
SQL> select /*+demo*/object_id, owner from t where object_id=1000;
已选择72729行。
统计信息
--------------------------------------
0 recursive calls
0 db block gets
11157 consistent gets
72729 rows processed
此时shared cursor状态如下：
SQL> select sql_id, executions, version_count, first_load_time from v$sqlarea where sql_text like 'select /*+demo*/%';
SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME
------------- ---------- ------------- ----------------------------------------------------------------------------
cnb0ktgvms6vq 3 1 2014-01-06/00:04:29
执行计划是进行Index Range Scan动作。
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'cnb0ktgvms6vq'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID cnb0ktgvms6vq, child number 0
-------------------------------------
select /*+demo*/object_id, owner from t where object_id=1000
Plan hash value: 514881935
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 11 | 2 (0)| 00
|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
19 rows selected
收集统计量，使用no_invalidate为false取值。
SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,no_invalidate => false,method_opt => 'for columns size 10 object_id');
PL/SQL procedure successfully completed
第四次执行过程。
SQL> select /*+demo*/object_id, owner from t where object_id=1000;
已选择72729行。
统计信息
----------------------------
141 recursive calls
0 db block gets
5835 consistent gets
72729 rows processed
SQL> select sql_id, executions, version_count, first_load_time from v$sqlarea where sql_text like 'select /*+demo*/%';
SQL_ID EXECUTION