本文主要是介绍固定执行计划-测试步骤，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
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'));
这篇关于固定执行计划-测试步骤的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
