第57期 如何排查Oracle数据库中某个sql的性能是否退化还是提升了

当Oracle数据库整体性能下降时，需要进行排查和跟踪，以确定问题的根本原因并采取适当的措施。本文将介绍一些常用的方法和技术，帮助您诊断和解决Oracle数据库性能下降的问题。

一、识别目标SQL

使用SQL语句排查Oracle数据库SQL性能退化/提升的完整方法： 查询高负载的SQL，有很多种方法。

-- 1. 查找高负载SQL
SELECT sql_id, 
       sql_text,
       executions,
       ROUND(elapsed_time/1000000, 2) elapsed_sec,
       ROUND(elapsed_time/1000000/NULLIF(executions,0), 4) avg_sec_per_exec,
       buffer_gets,
       disk_reads,
       rows_processed
FROM v$sqlstats 
WHERE elapsed_time > 1000000  -- 过滤执行时间>1秒
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;
-- 2. 查询数据库中正在执行的sql，按照执行时间排序，执行时间长的排在最前面
select a.USERNAME,
       a.MACHINE,
       c.spid,
       a.sid,
       a.serial#,
       a.p1,
       a.p1raw,
       a.p2,
       a.event,
       a.last_call_et real_time,
       b.sql_text,
       b.sql_fulltext,
       b.sql_id,
       a.prev_sql_id,
       a.program,
       a.machine,
       round(b.cpu_time / greatest(executions, 1)) cpu_time,
       round(b.buffer_gets / greatest(executions, 1)) buffer_gets,
       round(b.disk_reads / greatest(executions, 1)) disk_reads,
       round(b.elapsed_time / greatest(executions, 1) / 1000000) elapsed_time,
       b.executions,
       a.status,
       b.last_active_time,
       b.FIRST_LOAD_TIME,
       'alter system kill session ' || '''' || a.sid || ',' || a.serial# || '''' || ';'
  from gv$session a, gv$sql b, gv$process c
 where a.wait_class <> 'Idle'
   and a.sql_id = b.sql_id(+)
   and a.paddr = c.addr
   and a.sql_child_number = b.child_number
 order by last_call_et desc;

二、收集SQL性能历史数据

1. 创建性能监控表

-- 创建历史性能表
CREATE TABLE sql_perf_history (
    sql_id VARCHAR2(13),
    plan_hash_value NUMBER,
    capture_time DATE,
    executions NUMBER,
    elapsed_time_total NUMBER,
    elapsed_time_per_exec NUMBER,
    buffer_gets_total NUMBER,
    buffer_gets_per_exec NUMBER,
    disk_reads_total NUMBER,
    disk_reads_per_exec NUMBER,
    rows_processed_total NUMBER,
    module VARCHAR2(48),
    CONSTRAINT sql_perf_pk PRIMARY KEY (sql_id, plan_hash_value, capture_time)
);
-- 创建索引
CREATE INDEX idx_sql_perf_time ON sql_perf_history(capture_time);
CREATE INDEX idx_sql_perf_sqlid ON sql_perf_history(sql_id);

2. 定期收集性能快照

-- 手动收集当前性能快照
INSERT INTO sql_perf_history
SELECT 
    sql_id,
    plan_hash_value,
    SYSDATE,
    executions,
    elapsed_time,
    ROUND(elapsed_time/NULLIF(executions,0)/1000, 2),  -- 毫秒
    buffer_gets,
    ROUND(buffer_gets/NULLIF(executions,0), 2),
    disk_reads,
    ROUND(disk_reads/NULLIF(executions,0), 2),
    rows_processed,
    module
FROM v$sql
WHERE sql_id = '&your_sql_id'  -- 替换为你的SQL_ID
  AND parsing_schema_id IS NOT NULL;

三、性能趋势分析

1. 基本性能对比

-- 对比不同时间段性能
WITH current_stats AS (
    SELECT 
        sql_id,
        plan_hash_value,
        executions,
        ROUND(elapsed_time/NULLIF(executions,0)/1000, 2) as avg_elapsed_ms,
        ROUND(buffer_gets/NULLIF(executions,0), 2) as avg_buffer_gets,
        ROUND(disk_reads/NULLIF(executions,0), 2) as avg_disk_reads
    FROM v$sql
    WHERE sql_id = 'your_sql_id'
),
history_stats AS (
    SELECT 
        sql_id,
        plan_hash_value,
        MAX(elapsed_time_per_exec) as hist_max_elapsed,
        MIN(elapsed_time_per_exec) as hist_min_elapsed,
        AVG(elapsed_time_per_exec) as hist_avg_elapsed,
        AVG(buffer_gets_per_exec) as hist_avg_buffer_gets
    FROM sql_perf_history
    WHERE sql_id = 'your_sql_id'
      AND capture_time >= SYSDATE - 7  -- 最近7天历史
    GROUP BY sql_id, plan_hash_value
)
SELECT 
    c.sql_id,
    c.plan_hash_value as current_plan,
    c.avg_elapsed_ms as current_elapsed_ms,
    h.hist_avg_elapsed as hist_avg_elapsed_ms,
    ROUND(c.avg_elapsed_ms / NULLIF(h.hist_avg_elapsed, 0), 2) as elapsed_ratio,
    CASE 
        WHEN c.avg_elapsed_ms > h.hist_avg_elapsed * 1.5 THEN '性能退化'
        WHEN c.avg_elapsed_ms < h.hist_avg_elapsed * 0.7 THEN '性能提升'
        ELSE '性能稳定'
    END as performance_status
FROM current_stats c
LEFT JOIN history_stats h ON c.sql_id = h.sql_id;

2. 详细时间趋势分析

-- SQL性能时间趋势图数据
SELECT 
    TO_CHAR(capture_time, 'YYYY-MM-DD HH24') as capture_hour,
    plan_hash_value,
    COUNT(*) as execution_count,
    ROUND(AVG(elapsed_time_per_exec), 2) as avg_elapsed_ms,
    ROUND(MAX(elapsed_time_per_exec), 2) as max_elapsed_ms,
    ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY elapsed_time_per_exec), 2) as p95_elapsed_ms,
    ROUND(AVG(buffer_gets_per_exec), 2) as avg_buffer_gets,
    ROUND(AVG(disk_reads_per_exec), 2) as avg_disk_reads
FROM sql_perf_history
WHERE sql_id = 'your_sql_id'
  AND capture_time >= SYSDATE - 3  -- 最近3天
GROUP BY TO_CHAR(capture_time, 'YYYY-MM-DD HH24'), plan_hash_value
ORDER BY capture_hour;

四、执行计划变化分析

1. 检查执行计划稳定性

-- 执行计划变化历史
SELECT 
    TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI') as snap_time,
    s.plan_hash_value,
    s.executions_delta as execs,
    ROUND(s.elapsed_time_delta/1000/NULLIF(s.executions_delta,0), 2) as avg_ms_per_exec,
    ROUND(s.buffer_gets_delta/NULLIF(s.executions_delta,0), 2) as avg_buffer_gets,
    ROUND(s.disk_reads_delta/NULLIF(s.executions_delta,0), 2) as avg_disk_reads,
    ROUND(s.rows_processed_delta/NULLIF(s.executions_delta,0), 2) as avg_rows
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot sn ON s.snap_id = sn.snap_id AND s.instance_number = sn.instance_number
WHERE s.sql_id = 'your_sql_id'
  AND s.executions_delta > 0
  AND sn.begin_interval_time >= SYSDATE - 7
ORDER BY sn.begin_interval_time DESC;

2. 对比不同执行计划性能

-- 对比不同执行计划的性能差异
SELECT 
    plan_hash_value,
    COUNT(*) as plan_count,
    SUM(executions) as total_executions,
    ROUND(AVG(elapsed_time_per_exec), 2) as avg_elapsed_ms,
    ROUND(STDDEV(elapsed_time_per_exec), 2) as stddev_elapsed,
    ROUND(AVG(buffer_gets_per_exec), 2) as avg_buffer_gets,
    ROUND(AVG(disk_reads_per_exec), 2) as avg_disk_reads
FROM (
    SELECT 
        plan_hash_value,
        executions,
        elapsed_time_per_exec,
        buffer_gets_per_exec,
        disk_reads_per_exec
    FROM sql_perf_history
    WHERE sql_id = 'your_sql_id'
      AND executions > 0
) 
GROUP BY plan_hash_value
ORDER BY avg_elapsed_ms DESC;

五、自动化监控脚本

1. 创建性能退化检测视图

-- 创建性能退化监控视图
CREATE OR REPLACE VIEW sql_performance_degradation AS
WITH current_perf AS (
    SELECT 
        sql_id,
        plan_hash_value,
        ROUND(AVG(elapsed_time_per_exec), 2) as current_avg_ms
    FROM sql_perf_history
    WHERE capture_time >= SYSDATE - 1/24  -- 最近1小时
    GROUP BY sql_id, plan_hash_value
),
baseline_perf AS (
    SELECT 
        sql_id,
        plan_hash_value,
        ROUND(AVG(elapsed_time_per_exec), 2) as baseline_avg_ms,
        ROUND(STDDEV(elapsed_time_per_exec), 2) as baseline_stddev
    FROM sql_perf_history
    WHERE capture_time BETWEEN SYSDATE - 7 AND SYSDATE - 1  -- 一周前到昨天
    GROUP BY sql_id, plan_hash_value
    HAVING COUNT(*) >= 10  -- 至少有10个样本
)
SELECT 
    c.sql_id,
    c.plan_hash_value,
    c.current_avg_ms,
    b.baseline_avg_ms,
    b.baseline_stddev,
    ROUND(c.current_avg_ms / NULLIF(b.baseline_avg_ms, 0), 2) as degradation_ratio,
    CASE 
        WHEN c.current_avg_ms > b.baseline_avg_ms + (b.baseline_stddev * 3) THEN '严重退化'
        WHEN c.current_avg_ms > b.baseline_avg_ms * 1.5 THEN '中度退化'
        WHEN c.current_avg_ms > b.baseline_avg_ms * 1.2 THEN '轻微退化'
        WHEN c.current_avg_ms < b.baseline_avg_ms * 0.8 THEN '性能提升'
        ELSE '性能稳定'
    END as status
FROM current_perf c
JOIN baseline_perf b ON c.sql_id = b.sql_id
WHERE c.current_avg_ms > b.baseline_avg_ms * 1.2  -- 只显示退化的
ORDER BY degradation_ratio DESC;

2. 实时监控查询

-- 实时查看性能退化SQL
SELECT * FROM sql_performance_degradation
WHERE status IN ('严重退化', '中度退化')
ORDER BY degradation_ratio DESC;

六、详细诊断报告

1. 生成完整性能分析报告

-- 完整性能分析报告
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN sql_id FORMAT A13
COLUMN plan_hash_value FORMAT 9999999999
COLUMN time_period FORMAT A20
COLUMN performance_trend FORMAT A15
WITH performance_summary AS (
    SELECT 
        sql_id,
        plan_hash_value,
        CASE 
            WHEN capture_time >= SYSDATE - 1 THEN '最近24小时'
            WHEN capture_time >= SYSDATE - 7 THEN '最近7天'
            ELSE '历史基线'
        END as time_period,
        COUNT(*) as sample_count,
        ROUND(AVG(elapsed_time_per_exec), 2) as avg_elapsed_ms,
        ROUND(MIN(elapsed_time_per_exec), 2) as min_elapsed_ms,
        ROUND(MAX(elapsed_time_per_exec), 2) as max_elapsed_ms,
        ROUND(AVG(buffer_gets_per_exec), 2) as avg_buffer_gets,
        ROUND(AVG(disk_reads_per_exec), 2) as avg_disk_reads
    FROM sql_perf_history
    WHERE sql_id = '&input_sql_id'
    GROUP BY sql_id, plan_hash_value, 
        CASE 
            WHEN capture_time >= SYSDATE - 1 THEN '最近24小时'
            WHEN capture_time >= SYSDATE - 7 THEN '最近7天'
            ELSE '历史基线'
        END
)
SELECT 
    sql_id,
    plan_hash_value,
    time_period,
    sample_count,
    avg_elapsed_ms,
    ROUND((avg_elapsed_ms - LAG(avg_elapsed_ms) OVER (PARTITION BY sql_id, plan_hash_value ORDER BY 
        CASE time_period
            WHEN '最近24小时' THEN 1
            WHEN '最近7天' THEN 2
            ELSE 3
        END)) / NULLIF(LAG(avg_elapsed_ms) OVER (PARTITION BY sql_id, plan_hash_value ORDER BY 
        CASE time_period
            WHEN '最近24小时' THEN 1
            WHEN '最近7天' THEN 2
            ELSE 3
        END), 0) * 100, 2) as pct_change,
    CASE 
        WHEN avg_elapsed_ms > LAG(avg_elapsed_ms) OVER (PARTITION BY sql_id, plan_hash_value ORDER BY 
            CASE time_period
                WHEN '最近24小时' THEN 1
                WHEN '最近7天' THEN 2
                ELSE 3
            END) * 1.3 THEN '↑退化'
        WHEN avg_elapsed_ms < LAG(avg_elapsed_ms) OVER (PARTITION BY sql_id, plan_hash_value ORDER BY 
            CASE time_period
                WHEN '最近24小时' THEN 1
                WHEN '最近7天' THEN 2
                ELSE 3
            END) * 0.8 THEN '↓提升'
        ELSE '→稳定'
    END as performance_trend
FROM performance_summary
ORDER BY plan_hash_value, 
    CASE time_period
        WHEN '最近24小时' THEN 1
        WHEN '最近7天' THEN 2
        ELSE 3
    END;

七、使用建议

1. 定期运行收集脚本：建议每小时收集一次性能快照
2. 设置告警阈值：当 degradation_ratio > 1.5时发送告警
3. 结合AWR报告：上述SQL可与AWR历史数据结合分析
4. 监控关键SQL：重点关注TOP 20耗时SQL的性能趋势
5. 版本对比：应用发布前后对比SQL性能

-- 清理90天前的历史数据
DELETE FROM sql_perf_history 
WHERE capture_time < SYSDATE - 90;
-- 定期归档
CREATE TABLE sql_perf_history_archive 
AS SELECT * FROM sql_perf_history 
WHERE capture_time < SYSDATE - 365;