从执行计划上看,走的是索引IDX_T2上的索引范围扫描,因为SQL只执行了一次,所以Oracle不会自动捕获SQL Plan Baseline,DBA_SQL_PLAN_BASELINES中没有记录
zx@MYDB>col sql_handle for a30
zx@MYDB>col plan_name for a30
zx@MYDB>col origin for a20
zx@MYDB>col sql_text for a70
zx@MYDB>select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines;
no rows selected
再次执行上述SQL,因为重复执行该SQL,Oracle自动捕获了这个SQL的SQL Plan Baseline
zx@MYDB>select object_id,object_name from t2 where object_id between 103 and 108;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
103 MIGRATE$
104 DEPENDENCY$
105 ACCESS$
106 I_DEPENDENCY1
107 I_DEPENDENCY2
108 I_ACCESS1
6 rows selected.
zx@MYDB>select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------------- --------- --------- ------------------------------------------------------------
SYS_SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE YES YES select object_id,object_name from t2 where object_id between 103 and 108
现在将索引IDX_T2的聚簇因子修改为2400万,目的是为了能让SQL的执行计划变为对表T2的全表扫描(为何修改聚簇因子,参考 )。修改完后再执行上述SQL,并查看执行计划:
zx@MYDB>exec dbms_stats.set_index_stats(ownname=>USER,indname=>'IDX_T2',clstfct=>24000000,no_invalidate=>false);
PL/SQL procedure successfully completed.
zx@MYDB>select index_name,clustering_factor from dba_indexes where index_name='IDX_T2';
INDEX_NAME CLUSTERING_FACTOR
------------------------------------------------------------------------------------------ -----------------