作者微信
微

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

小技术 7个月前 (07-12) 0 999+

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;

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

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;