SPB[@more@]

手工把 plan 改为 accepted 状态；只有处于 accepted 状态的 plan 才可能被用来执行 sql :

variable v_sql_handle varchar2(30);

variable v_plan_name_full varchar2(30);

variable v_plan_name_index varchar2(30);

exec :v_sql_handle := 'SYS_SQL_0993bcb3526c1c35';

exec :v_plan_name_full := 'SQL_PLAN_a8qny22gsbtd494ecae5c';

exec :v_plan_name_index := 'SQL_PLAN_a8qny22gsbtd40893a4b2';

variable v_report clob;

exec :v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => :v_sql_handle);

print :v_report

把 plan 的 enabled 属性改为 NO 。此时的 plan 不会被选用。

variable v_sql_handle varchar2(30);

variable v_plan_name_full varchar2(30);

variable v_plan_name_index varchar2(30);

variable v_basenum number;

exec :v_sql_handle := 'SYS_SQL_0993bcb3526c1c35';

exec :v_plan_name_index := 'SQL_PLAN_0m4xwqd96s71p695cc014';

exec :v_basenum:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name => :v_plan_name_index,attribute_name => 'ENABLED', attribute_value => 'NO');

ITPub博客

Oracle SQL Plan Baseline 学习

最新文章