SPM_rewrite
emcs
34次下载
654次浏览
2019-07-24
4.5

脚本内容

-- 给特定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 ;

评论

贡献排行榜