--这次是个测试,先绑定慢的执行计划,然后恢复成原来的
--需要先绑定一个执行计划得到sql_handle
--绑定慢的
declare
l_pls number;
begin
l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '0wtys438rtrc9',
plan_hash_value => 3681313433,
enabled => 'YES');
end;
/
--绑定正常的
declare
l_pls number;
begin
l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'gh958h3j4s71m',
plan_hash_value => 2612465696,
enabled => 'YES');
end;
/
sql语句
--正常执行计划
select * from test_mv
较慢的执行计划
select /*+index (test_mv FK_NAME)*/ * from test_mv
--正式修改执行计划
--查询得到sql_id,plan_hash_value,sql_handle,plan_name
--sql_id
select * from v$sql where sql_text like '%test_mv%';
--plan_hash_value
SELECT DISTINCT(PLAN_HASH_VALUE) FROM V$SQL_PLAN t WHERE SQL_ID = 'gh958h3j4s71m' and child_number='0';
--sql_handle,plan_name
select * from dba_sql_plan_baselines;
--禁用正常的执行计划
declare
l_pls number;
begin
l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '0wtys438rtrc9',
plan_hash_value => 3681313433,
enabled => 'NO');
end;
/
--关联想要绑定的执行计划
declare
l_pls number;
begin
l_pls := DBMS_SPM.load_plans_from_cursor_cache(sql_id => 'gh958h3j4s71m', -- new_SQL_ID'
plan_hash_value => 2612465696, --new_plan_hash_value
sql_handle => 'SQL_aca6e5484aec92bc' --OLD_handle
);
end;
/
--删除原执行计划
declare
l_pls number;
begin
l_pls := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => 'SQL_190cf0b3ef22649b', --sql_handle_for_original
plan_name => 'SQL_PLAN_1k37hqgrk4t4vc625dc35' --sql_plan_name_for_original
);
end;
/