zx@MYDB>exec :temp := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SYS_SQL_ac526b1e4be74880',plan_name=>'SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name=>'accepted',attribute_value=>'NO');
BEGIN :temp := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SYS_SQL_ac526b1e4be74880',plan_name=>'SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name=>'accepted',attribute_value=>'NO'); END;
*
ERROR at line 1:
ORA-38136: invalid attribute name ACCEPTED specified
ORA-06512: at "SYS.DBMS_SPM", line 2469
ORA-06512: at line 1
在11gR2中，我们可以联合使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE和DBMS_SPM.ALTER_SQL_PLAN_BASELINE达到启用目标SQL新的执行计划的目的。
先用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE将新的执行计划(全表扫描)所对应的SQL Plan Baseline的ACCEPTED值设为“YES”：
zx@MYDB>exec :temp := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_ac526b1e4be74880',plan_name=>'SQL_PLAN_asnmb3t5yfk40b860bcf2',verify=>'NO',commit=>'YES');
PL/SQL procedure successfully completed.
从上面显示的内容看到如下信息：“Plan: SQL_PLAN_asnmb3t5yfk40b860bcf2----Plan was changed to an accepted plan.”，这表明已经将新的执行计划(全表扫描)所对应的SQL Plan Baseline的ACCEPTED值设为YES
从下面的查询结果也可以证明：
zx@MYDB>select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
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
SYS_SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk40b860bcf2 AUTO-CAPTURE YES YES select object_id,object_name from t2 where object_id between 103 and 108
然后再使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE将原先的执行计划(索引范围扫描)对应的SQL Plan Baseline的ENABLED的值设为NO：
zx@MYDB>exec :temp := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SYS_SQL_ac526b1e4be74880',plan_name=>'SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name=>'enabled',attribute_value=>'NO');
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 object_id%';
SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------------- --------- --------- ----------------------------------------------------------------------
SYS_SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE NO YES select object_id,object_name from t2 where object_id between 103 and 108
SYS_SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk40b860bcf2 AUTO-CAPTURE YES YES select object_id,object_name from t2 where object_id between 103 and 108
再次执行目标SQL
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.