第53期 排查分析最近30天内SQL语句性能出现退化的SQL

DBA_HIST_SQLSTAT 显示 SQL 统计信息的历史数据。

此视图基于一系列标准，从 V$SQL 获取TOP SQL 语句及其统计信息。总计值表示自实例启动以来的累计统计值。增量值表示 DBA_HIST_SNAPSHOT 视图中 BEGIN_INTERVAL_TIME 到 END_INTERVAL_TIME 时间间隔内的统计值。

此视图与 DBA_HIST_OPTIMIZER_ENV、DBA_HIST_SQLTEXT 和 DBA_HIST_SQL_PLAN 视图结合使用，以提供 SQL 统计信息的完整历史记录。 我们可以根据此试图分析最近30天内SQL语句的性能稳定性，识别出执行时间出现退化的SQL 。

SELECT sq.sql_id,
       sq.plan_hash_value,
       COUNT(DISTINCT sq.snap_id) as period_count,
       MIN(sn.begin_interval_time) as first_seen,
       MAX(sn.end_interval_time) as last_seen,
       ROUND(MIN(sq.elapsed_time_delta / NULLIF(sq.executions_delta, 0) /
                 1000000),
             4) as min_elapsed_sec,
       ROUND(MAX(sq.elapsed_time_delta / NULLIF(sq.executions_delta, 0) /
                 1000000),
             4) as max_elapsed_sec,
       ROUND(MAX(sq.elapsed_time_delta / NULLIF(sq.executions_delta, 0) /
                 1000000) /
             NULLIF(MIN(sq.elapsed_time_delta /
                        NULLIF(sq.executions_delta, 0) / 1000000),
                    0),
             2) as perf_variance,
       CASE
         WHEN MAX(sq.elapsed_time_delta / NULLIF(sq.executions_delta, 0) /
                  1000000) /
              NULLIF(MIN(sq.elapsed_time_delta /
                         NULLIF(sq.executions_delta, 0) / 1000000),
                     0) > 5 THEN
          '严重退化'
         WHEN MAX(sq.elapsed_time_delta / NULLIF(sq.executions_delta, 0) /
                  1000000) /
              NULLIF(MIN(sq.elapsed_time_delta /
                         NULLIF(sq.executions_delta, 0) / 1000000),
                     0) > 2 THEN
          '轻微退化'
         ELSE
          '稳定'
       END as stability
  FROM dba_hist_sqlstat sq
  JOIN dba_hist_snapshot sn
    ON sq.snap_id = sn.snap_id
 WHERE sq.executions_delta > 10
   AND sq.elapsed_time_delta > 0
   AND sn.begin_interval_time > SYSDATE - 30 -- 最近30天
 GROUP BY sq.sql_id, sq.plan_hash_value
HAVING COUNT(DISTINCT sq.snap_id) >= 3 -- 至少出现在3个时间段
 ORDER BY perf_variance DESC;

这段SQL语句的作用是从Oracle的AWR历史数据中，分析SQL语句的性能稳定性。具体来说，它通过关联dba_hist_sqlstat和dba_hist_snapshot视图，获取最近30天内出现至少3个不同时间段（快照）的SQL语句，并计算其执行时间的波动情况，从而判断SQL性能是否出现退化。

步骤解析：

从dba_hist_sqlstat（SQL历史统计信息）和dba_hist_snapshot（快照历史）中选取数据。

1. 关联条件为sq.snap_id = sn.snap_id。

2. 筛选条件：

   每个快照周期内执行次数大于10（sq.executions_delta > 10），这是为了排除执行次数太少导致的偶然性。

3. 每个快照周期内总耗时大于0（sq.elapsed_time_delta > 0）。

4. 快照开始时间在最近30天内（sn.begin_interval_time > SYSDATE - 30）。

5. 按照SQL_ID和计划哈希值（plan_hash_value）分组，这样同一个SQL语句如果执行计划不同（即不同plan_hash_value）会被分开统计。

6. 分组后，只选择出现在至少3个不同快照周期中的SQL（COUNT(DISTINCT sq.snap_id) >= 3）。

7. 计算指标：

   period_count: 该SQL出现在多少个不同的快照周期中。

8. first_seen: 该SQL第一次出现的时间（最早快照开始时间）。

9. last_seen: 该SQL最后一次出现的时间（最晚快照结束时间）。

10. min_elapsed_sec: 每个快照周期内，每次执行的平均耗时（秒）的最小值。注意：elapsed_time_delta是微秒，除以1000000得到秒。

11. max_elapsed_sec: 每个快照周期内，每次执行的平均耗时（秒）的最大值。

12. perf_variance: 性能波动，即最大平均耗时除以最小平均耗时。如果最小平均耗时为0，则除数为NULL，结果为NULL。

13. stability: 根据perf_variance的值判断稳定性。如果波动大于5倍，标记为“严重退化”；大于2倍，标记为“轻微退化”；否则为“稳定”。

14. 结果按照perf_variance降序排列，这样性能退化最严重的SQL排在最前面。

总结：该SQL用于监控最近30天内，执行次数较多（每个快照周期执行超过10次）且出现在至少3个快照周期中的SQL语句，分析其平均执行时间的变化，从而找出性能不稳定的SQL（特别是性能退化的SQL）。

使用场景：

1.  性能调优