使用SPM和STA進行固定執行計劃

gaopengtttt發表於2014-11-05
在實際的工作中可能遇到SQL執行計效能劃退化的可能性，除了研究退化原因以外，我們可能需要固定其執行計劃 
一、如下列子展示如果在多個執行計劃中使用SPM選擇正確的執行計劃，SPM預設是不會自動建立BASELINE的。但是可以修改引數開啟自動建立（optimizer_capture_sql_plan_baselines）
但是建議不要開啟，如果開啟自動捕獲，自動接受，那麼可能喪失CBO在判別到資料量變動自動調整SQL的可能，因為自動捕獲的第二個執行計劃其ACCPECT為NO，除非手動進行演化，演化會驗證每個BASELINE效率，從而改變其ACCPECT值。而手動建立的BASELINE其ACCPECT為YES，如下演示如何刪除和手動建立一個BASELINE

var temp varchar2(1000);

select 'execute :temp:=dbms_spm.drop_sql_plan_baseline(sql_handle=> ''' || name||''');'
 from (select distinct (sql_handle) name from dba_sql_plan_baselines)                 
 
 select executions,sql_id,a.PLAN_HASH_VALUE,a.CHILD_NUMBER from v$sql a where UPPER(a.SQL_FULLTEXT) like '%SELECT ID%SYN_EVENT%' AND EXECUTIONS>5;
 
 找到SQL_ID，PLAN_HASH_VALUE
EXECUTIONS SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER SQL_TEXT
---------- ------------- --------------- ------------ --------------------------------------------------------------------------------
       136 9dgag3t1yfp99       806173478            1 SELECT id ,event_id ,event_name ,event_type ,user_id ,            account_no , c
       885 9dgag3t1yfp99       676394365            4 SELECT id ,event_id ,event_name ,event_type ,user_id ,            account_no , c
        33 d81hzszzxzwcr       676394365            0 SELECT id ,event_id ,event_name ,event_type ,user_id ,            account_no , c
 
 execute :temp :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'9dgag3t1yfp99',plan_hash_value =>676394365);

 SELECT * FROM dba_sql_plan_baselines;可以檢視是否正常
 
二、如下列子展示如何使用STA分析語句執行計劃的錯誤，使用PROFILE進行固定，用於可能的執行計劃有誤，而沒有正確的執行計劃，也就是說沒有出現多個子遊標。
 1、
 variable stmt_task VARCHAR2(64);
 variable sts_task VARCHAR2(64);
 2、找到
 SQL_ID PLAN_HASH_VALUE
 進行建立調整任務
 EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '43914a5zg199h',plan_hash_value=>'2578872466');
 
 3、執行調整任務
 EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task);
 
 4、找到其 TASK_NAME和EXE_NAME
 
 select TASK_NAME,LAST_EXECUTION from dba_advisor_tasks where task_name='TASK_2291';
 5、檢視報告
 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(task_name => 'TASK_2291',execution_name =>'EXEC_2296' ) from DUAL;
 6、如果分析正確可以啟用PROFILE
 execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_2291',task_owner => 'DBADMIN', replace => TRUE,force_match =>TRUE);
 注意PROFILE 高於HINT優先順序別
 
 7、刪除
 execute DBMS_SQLTUNE.DROP_SQL_PROFILE (name => 'SYS_SQLPROF_01497da0ce600000');
 
 select * from  dba_sql_profiles;可以檢視你的新建立的profile

僅此記錄.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-1320965/，如需轉載，請註明出處，否則將追究法律責任。

相關文章