执行计划变化的处理
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'
||= '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
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的名字互换,让outline生效
UPDATE OUTLN.OL$HINTS
SET OL_NAME=DECODE(OL_NAME,'ZHUOFHB','ZHUOFHA','ZHUOFHA','ZHUOFHB')
WHERE OL_NAME IN ('ZHUOFHB','ZHUOFHA');
exec dbms_outln.update_signatures;
alter system set use_stored_outlines=special;
第四步:删除不用的outline
--这个一定要做,否则以后他们下发优化后的
sql语句后,会用到这个outline中的坏的执行计划
drop outline zhuofhb;
第五步:验证
验证执行计划,对
pol_main不走全表扫描就对了
select hash_value,sql_text,outline_category,child_number from v$sql where hash_value=3671808433
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22578826/viewspace-738011/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22578826/viewspace-738011/