【Oracle】记一次执行计划的调优

1.问题

针对以下SQL,用户反馈周日(7月3日)明显变慢。

SELECT T1.CONFLICT_ID,
T1.LAST_UPD,
T1.CREATED,
T1.LAST_UPD_BY,
T1.CREATED_BY,
T1.MODIFICATION_NUM,
T1.ROW_ID,
T1.SRA_SR_ID,
T1.TODO_PLAN_START_DT,
T1.TODO_PLAN_END_DT,
T1.TARGET_PER_ID,
T1.EVT_STAT_CD
FROM SIEBEL.S_EVT_ACT T1
WHERE ( T1.SRA_SR_ID IS NULL
AND T1.CALLED_FROM_NUM LIKE :1
AND T1.OWNER_PER_ID = :2
AND T1.TODO_PLAN_START_DT >= TO_DATE ( :3, 'MM/DD/YYYY HH24:MI:SS'))
ORDER BY T1.TODO_PLAN_START_DT DESC;

2.问题定位

对执行计划的变更历史进行查询查询结果如下:

SQL_ID       |PLAN_HASH_VALUE|TIMESTAMP        |
-------------+---------------+-----------------+
4ack6zgrpbvcf| 3363356005|20220703 09:12:34|
4ack6zgrpbvcf| 1929621957|20220704 10:36:33|

*PS:执行计划变更历史查询方法如下:

--SQL_ID通过ADDM REPORT中取得
select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') TIMESTAMP
from dba_hist_sql_plan
where SQL_ID='4ack6zgrpbvcf' order by TIMESTAMP;

再查出变更的明细,看前后的执行计划有什么差异,查询结果如下:

PLAN_HASH_VALUE|ID|OPERATION       |OPTIONS       |OBJECT_NAME |DEPTH|COST|TO_CHAR(TIMESTAMP,'YYYYMMDDHH24:MI:SS')|
---------------+--+----------------+--------------+------------+-----+----+---------------------------------------+
1929621957| 0|SELECT STATEMENT| | | 0|6767|20220704 10:36:33 |
1929621957| 1|SORT |ORDER BY | | 1|6767|20220704 10:36:33 |
1929621957| 2|TABLE ACCESS |BY INDEX ROWID|S_EVT_ACT | 2|6766|20220704 10:36:33 |
1929621957| 3|INDEX |RANGE SCAN |S_EVT_ACT_M6| 3| 164|20220704 10:36:33 |
3363356005| 0|SELECT STATEMENT| | | 0| 5|20220703 09:12:34 |
3363356005| 1|TABLE ACCESS |BY INDEX ROWID|S_EVT_ACT | 1| 5|20220703 09:12:34 |
3363356005| 2|INDEX |RANGE SCAN |S_EVT_ACT_M8| 2| 4|20220703 09:12:34 |

*PS:执行计划变更历史明细查询方法如下:

select plan_hash_value,id,operation,options,object_name,depth,cost,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss')
from DBA_HIST_SQL_PLAN
where sql_id ='4ack6zgrpbvcf'
and plan_hash_value in (3363356005,1929621957)
order by PLAN_HASH_VALUE,ID;

由此可知,执行计划变化前后,主要是使用的索引不一样,再将索引的信息查询如下:

(1)索引S_EVT_ACT_M6由OWNER_PER_ID, "APPT_START_DT"组合而成;

(2)索引S_EVT_ACT_M8由"TODO_PLAN_START_DT"单个字段构成。

对比SQL,明显索引(2)更为适合使用,通过时间的筛选可以大幅缩窄检索面,索引(1)简直是莫名其妙。(但从执行计划看,新的执行计划cost的确更低,可能是优化器发傻)

3.验证

对两个不同的执行计划进行验证:

(1)使用旧的执行计划,可以在SQL中指定索引S_EVT_ACT_M8,执行计划的cost达到233080,但执行时间只有497ms;

【Oracle】记一次执行计划的调优

(2)使用当前的执行计划,即默认使用索引S_EVT_ACT_M6,执行计划中的cost是17217,但执行结果却要34秒,与上述的497ms相去甚远,所以优化器的执行计划并不完全可信。

【Oracle】记一次执行计划的调优

4.问题原因

Oracle执行计划的变化很复杂,至于为什么选用了错误的执行计划,原因估计有以下:

(1)没有合适的索引,如果是性能需要保障的SQL且where条件较少,合理使用组合索引可以确保执行计划的稳定,但本表的索引超100个,不建议再增加;

(2)表的索引过多,导致优化器选择异常,建议可以适当清理无用的索引(此表200多个字段,100多个索引,这是什么神仙表啊~~)

5.解决方案

(1)创建组合索引。(~本表索引太多,不建议使用)

(2)对表重新收集统计信息,看一下执行计划是否会更改正常。(~收集过,没有变化)

  SELECT /*+ index(T1 S_EVT_ACT_M8)*/
T1.CONFLICT_ID,
T1.LAST_UPD,
T1.CREATED,
T1.LAST_UPD_BY,
T1.CREATED_BY,
T1.MODIFICATION_NUM,
T1.ROW_ID,
T1.SRA_SR_ID,
T1.TODO_PLAN_START_DT,
T1.TODO_PLAN_END_DT,
T1.TARGET_PER_ID,
T1.EVT_STAT_CD
FROM SIEBEL.S_EVT_ACT T1
WHERE ( T1.SRA_SR_ID IS NULL
AND T1.CALLED_FROM_NUM LIKE :1
AND T1.OWNER_PER_ID = :2
AND T1.TODO_PLAN_START_DT >= TO_DATE ( :3, 'MM/DD/YYYY HH24:MI:SS'))
ORDER BY T1.TODO_PLAN_START_DT DESC;
发表评论

相关文章