| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 30 | 335 (0)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T2@SEL$1
2 - SEL$1 / T2@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=4)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
2 - "T2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
46 rows selected.
zx@MYDB>exec :temp :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'60txg87j30pvw',plan_hash_value=>2008370210,sql_handle=>'SYS_SQL_75b06ae056223f5f');
PL/SQL procedure successfully completed.
zx@MYDB>select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(t2 idx_t2)%';
SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------------- --------- --------- ----------------------------------------------------------------------
SYS_SQL_75b06ae056223f5f SQL_PLAN_7bc3aw1b24guz24c6dbb6 MANUAL-LOAD YES YES select /*+ no_index(t2 idx_t2) */ object_name,object_id from t2 where object_i
d=4
SYS_SQL_75b06ae056223f5f SQL_PLAN_7bc3aw1b24guzb860bcf2 MANUAL-LOAD YES YES select /*+ no_index(t2 idx_t2) */ object_name,object_id from t2 where object_i
d=4
从上述输出可以看出把改写过的SQL的新的执行计划所对应的SQL Plan Baseline已经成功生成，而且所有手工生成的SQL Plan Baseline的ENABLED和ACCEPTED的值均为YES，这是和自动捕获的SQL Plan Baseline不一样的地方。
Drop掉原执行计划(全表扫描)所对应的SQL Plan Baseline：
zx@MYDB>exec :temp := dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_75b06ae056223f5f',plan_name=>'SQL_PLAN_7bc3aw1b24guzb860bcf2');
PL/SQL procedure successfully completed.
zx@MYDB>select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(t2 idx_t2)%';
SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------------- --------- --------- ----------------------------------------------------------------------