-- 给特定SQL绑上改写后的特定执行计划，需要事先查询得到对应的SNAP_ID,sql_id 和 plan_hash_value, COE_profile.sql 的SPM版
set serverout on
declare cnt int;
p_sql_handle varchar2(100);
v_sql_text_hash number;
p_bad_sqlid varchar2(32) default '&bad_sql_id';
p_good_sqlid varchar2(32) default '&good_sql_id';
p_good_hash number default '&good_plan_hash_value';
begin
-- Load old plan without enable
cnt:= dbms_spm.load_plans_from_cursor_cache(sql_id=>'&badsql',enabled=>'NO') ;
select ora_hash(to_char(dbms_lob.substr(sql_fulltext,1000))) into v_sql_text_hash from v$sql where rownum =1
union select  ora_hash(to_char(dbms_lob.substr(sql_fulltext,1000))) into v_sql_text_hash from v$sql where rownum =1;
dbms_output.put_line(cnt);
-- get last plan handle
select sql_handle into p_sql_handle
from dba_sql_plan_baselines 
where created > sysdate - 5/3600/24
and ora_hash(to_char(dbms_lob.substr(sql_text,1000))) = v_sql_text_hash;
dbms_output.put_line('Load '||cnt||' BAD Plan into baseline without enabled, SQL_HANDLE='||p_sql_handle);
-- Load good plan with old SQL
cnt:= dbms_spm.load_plans_from_cursor_cache(sql_id=>p_good_sqlid,plan_hash_value=> p_good_hash,sql_handle=>p_sql_handle) ;
dbms_output.put_line('Replaced '||cnt||' GOOD Plan into baseline');
end;
/
set serverout off
select CREATED,sql_handle,enabled,plan_name from dba_sql_plan_baselines ;