執行計劃變化的處理

oracle 10g

操作步驟

方法一：

先嚐試用oracle自帶的調優包得到建議，建議裡可以採用profile得到原來的執行計劃，則接受建議，執行相關命令；

如果建議裡沒有原來的執行計劃，可以轉到方法二執行。

注意：將下面SQL中的“9w45dnadms9”替換為問題SQL的 sql_id 即可執行。

步驟一、建立任務

set serverout on

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task(sql_id => '9w45dnadms9',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 600,

task_name => '9w45dnadms9_tuning_task',

description => 'Tuning task for statement 9w45dnadms9.');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

步驟二、確認任務已經建立

--有返回結果表示正常

select task_name

from dba_advisor_log

where wner = 'SYS'

and task_name = '9w45dnadm9s_tuning_task';

執行任務

Execute dbms_sqltune.Execute_tuning_task (task_name => '9w45dnadms9_tuning_task');

步驟三、確認任務狀態

select status

from dba_advisor_log

where task_name = '9w45dnadms9_tuning_task';

步驟四、列印報表

--檢視調優建議，這一步可以得到調優建議，如果有需要的執行計劃，則接受profile

set long 999999

set longchunksize 1000

set lin 140 pages 1000

select dbms_sqltune.report_tuning_task('9w45dnadms9_tuning_task')

from dual;

這裡舉個例子，如果確認執行計劃可用，執行紅框命令即可。

步驟五、刪除任務

--調優完成後此任務不再需要

exec dbms_sqltune.DROP_TUNING_TASK(task_name => '9w45dnadms9_tuning_task');

方法二：

因統計資訊變化導致執行計劃效率變差的情況，出現的頻率還是很高的。基本的思路如下，

步驟一、檢視歷史資訊

--透過看awr的歷史資訊，很容易就知道執行計劃的效率更高

select s.instance_number,

to_char(sn.end_interval_time, 'YYYYMMDD HH24:MI:SS'),

s.plan_hash_value,

s.executions_delta,

round(s.elapsed_time_delta / s.executions_delta),

round(s.BUFFER_GETS_delta / s.executions_delta),

round(s.CPU_TIME_delta / s.executions_delta)

from dba_hist_snapshot sn, sys.WRH$_SQLSTAT s

where s.snap_id = sn.snap_id

and s.sql_id = '1gu8t96d0bdmu'

and s.instance_number = sn.instance_number

and s.executions_delta > 0

order by sn.end_interval_time desc

步驟二、對比新舊執行計劃區別

--對比效率高和低的執行計劃的區別

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id',null,null,'ALL'));

步驟三、做hint 得到好的執行計劃

一般情況下，透過index， leading，use_hash or use_nl 就能搞定，估計80%~90%的sql用這幾個hint就能搞定了

對於10g以上庫，有個理論上方法，將DG 設定迴歸點後啟用，將sql相關的物件的統計資訊回退到好執行計劃的時間點對 explain plan for sqlstat 做10053 的trace，好的執行計劃的hint就能直接在trace檔案中找到

步驟四、outline 互換

第一步：先為加Hint後的好的執行計劃生成outline：

CREATE OR REPLACE OUTLINE zhuofhb for category special ON

SELECT T1.DISPATCHNO,

T1.AUTO_TASK_NO,

T1.TASK_NO,

T1.REPORT_ID,

T1.TASK_DEPT,

T1.HANDLER_ID,

T1.CAR_MARK,

T1.ARRIVE_TIME,

T1.ARRIVE_TIMES,

T1.TASK_STATUS,

T1.APPLY_USER,

T1.APPLY_TACHE_ID,

T1.TASK_ATTRIBUTE,

T1.REMARK,

T1.CREATED_BY,

T1.CREATED_DATE,

T1.UPDATE_BY,

T1.UPDATE_DATE,

T1.LOCAL_FLAG

FROM T_AUTO_DISPATCH_TASK T1

WHERE (T1.TASK_STATUS = '2' OR T1.AUTO_TASK_NO IS NULL)

AND EXISTS (SELECT *

FROM (SELECT DISTINCT T.DISPATCHNO

FROM (SELECT T2.DISPATCHNO

FROM T_AUTO_DISPATCH_COMMON T2

WHERE T2.SYNCSTATUS = '0'

AND T2.REPORTFLAG = 'Y'

AND T2.CASE_STATUS = '1'

AND EXISTS

(SELECT 1

FROM C_ACC_SYNC_INFO T

WHERE T.SYNC_STATUS
||= '2'

AND T.CASENO = T2.CASENO)

ORDER BY T2.CASE_DIS_TIME) T

WHERE ROWNUM < 20) XX

WHERE XX.DISPATCHNO = T1.DISPATCHNO)

ORDER BY T1.ARRIVE_TIME

;

第二步：再為舊的壞的執行計劃生成outline：

CREATE OR REPLACE OUTLINE zhuofha for category special ON