固定执行计划-测试步骤

0、环境准备，由于t有索引，该语句默认走索引

如果抓取优化参数未开启，也无法抓取

optimizer_capture_sql_plan_baselines boolean TRUE

alter system set optimizer_capture_sql_plan_baselines =true scope=both;

select count(*) from t where user_id=75;

select sql_id,PLAN_HASH_VALUE,sql_text from v$sql where sql_text like '%user_id=75%';

SQL_ID:84aytmtdzdh0j

PLAN_HASH_VALUE：878720519

1、创建指定SQLID的BASELINE,后面要做修改

declare

l_pls number;

begin

l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(

sql_id => '84aytmtdzdh0j',

plan_hash_value => 878720519,

enabled => 'NO');

end;

/

select count(*) from t where user_id=75;

2、构造出执行计划为全表扫描的SQL_ID，即：正确的执行计划

select /*+ full(t) */count(*) from t where user_id=75;

select sql_id,PLAN_HASH_VALUE,sql_text from v$sql where sql_text like '%user_id=75%';

SQL_ID:0pvkzjqwdgkbt

PLAN_HASH_VALUE：2966233522

3、确定原始执行计划的 sql_handle

select sql_handle, plan_name, origin, enabled, accepted,fixed,optimizer_cost,sql_text from dba_sql_plan_baselines where sql_text like '%user_id=75%' order by last_modified;

SQL_HANDLE:SQL_17ff9d394b036628

PLAN_NAME:SQL_PLAN_1gzwx755h6tj8fa7e82c5

4、与正确的执行计划关联

declare l_pls number;

begin

l_pls := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '0pvkzjqwdgkbt', --正确执行计划的sql_id

plan_hash_value =>2966233522, --正确执行计划的plan_hash_value

sql_handle => 'SQL_17ff9d394b036628' --原执行计划的sql_handle

);

end;

/

5、删除错误执行计划(可选)

declare l_pls number;

begin

l_pls := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => 'SQL_17ff9d394b036628', --sql_handle_for_original

plan_name => 'SQL_PLAN_1gzwx755h6tj8fa7e82c5' --sql_plan_name_for_original

);

end;

/

6、验证

explain plan for select count(*) from t where user_id=75;

select * from table(dbms_xplan.display);

7、（其他）查询所有执行计划

select * from table(dbms_xplan.display_cursor('sql_id',NULL,'OUTLINE'));