Oracle 慢 SQL 怎么查？这一套组合拳够用了

前言

做 DBA 应该经常碰到这种事情，业务突然发过来一条消息：

这条 SQL 最近很慢，帮忙看一下。

这种问题处理多了以后，我现在拿到 SQL 已经很少第一时间去想“是不是缺索引”了。一条 SQL 慢，可能是 Physical Reads 突然上来了，也可能是 Buffer Gets 本身就很高；还有些 SQL 读的数据并不多，时间其实耗在锁等待、RAC 或其他 Wait Event 上。

所以我现在拿到 SQL_ID，我一般先看几个最直接的指标：

Physical Reads
      ↓
Buffer Gets（逻辑读）
      ↓
Rows Processed
      ↓
Elapsed Time

先把 SQL 慢在哪里定下来，再去翻 AWR、看执行计划、查 Stats、索引、选择性和 Bind。下面这套查询基本就是我现在常用的完整路径，拿到 SQL_ID 后可以一路往下查。

只有 SQL Text，先找到 SQL_ID

如果业务已经给了 SQL_ID，这一步直接跳过。如果只有 SQL Text，或者只知道涉及某张表，可以先从 Shared Pool 找。RAC 环境建议直接查 GV$SQL：

SET LINES 300
SET PAGES 200
SET LONG 1000000
SET LONGCHUNKSIZE 1000000

SELECT inst_id,
       sql_id,
       child_number,
       plan_hash_value,
       executions,
       buffer_gets,
       disk_reads,
       rows_processed,
       ROUND(elapsed_time/1e6,2) elapsed_s,
       TO_CHAR(last_active_time,'YYYY-MM-DD HH24:MI:SS') last_active
FROM gv$sql
WHERE UPPER(sql_fulltext) LIKE '%PE_BATCH_PROCESS%'
ORDER BY last_active_time DESC;

找到 SQL_ID 后，把完整 SQL 拿出来：

SELECT sql_fulltext
FROM gv$sql
WHERE sql_id='&sql_id'
  AND ROWNUM=1;

如果 SQL 已经不在 Shared Pool，可以继续从 AWR 找：

SELECT sql_id,
       sql_text
FROM dba_hist_sqltext
WHERE UPPER(sql_text) LIKE '%PE_BATCH_PROCESS%';

先把 SQL_ID 确定下来，后面的当前执行情况、历史表现、执行计划和 Bind 才能串到一起。

拿到 SQL_ID，先看当前资源画像

我一般先把单次执行的几个核心指标一次查出来：

SET LINES 300
SET PAGES 200

SELECT inst_id,
       sql_id,
       child_number,
       plan_hash_value,
       executions,
       buffer_gets,
       disk_reads,
       rows_processed,
       ROUND(disk_reads/NULLIF(executions,0)) reads_per_exec,
       ROUND(buffer_gets/NULLIF(executions,0)) gets_per_exec,
       ROUND(rows_processed/NULLIF(executions,0)) rows_per_exec,
       ROUND(elapsed_time/1e6/NULLIF(executions,0),2) elapsed_per_exec_s,
       ROUND(cpu_time/1e6/NULLIF(executions,0),2) cpu_per_exec_s,
       TO_CHAR(last_active_time,'YYYY-MM-DD HH24:MI:SS') last_active
FROM gv$sql
WHERE sql_id='&sql_id'
ORDER BY inst_id,child_number;

第一眼先看 Reads/Exec。Physical Reads 明显升高，SQL 很容易从几秒变成几十秒；如果 Physical Reads 不高，再看 Gets/Exec，判断 SQL 本身是不是做了大量逻辑访问。Buffer Gets 很高时还要结合 Rows/Exec，因为“为了返回几十行访问几百万个块”和“本身就要处理几百万行”不是一回事。

大致可以先按下面的方式定方向：

这里先做初步定性，不急着改 SQL。

Reads 和 Gets 都解释不了，再看 Wait

如果 Physical Reads 和 Buffer Gets 都不高，但 Elapsed/Exec 仍然很高，就应该先确认时间到底花在哪里。

SQL 正在执行时：

SET LINES 300
SET PAGES 200

SELECT inst_id,
       sid,
       serial#,
       username,
       sql_id,
       status,
       event,
       wait_class,
       seconds_in_wait,
       blocking_instance,
       blocking_session,
       module,
       program
FROM gv$session
WHERE sql_id='&sql_id'
ORDER BY inst_id,sid;

如果 SQL 已经执行结束，可以从 ASH 看历史状态：

SELECT NVL(event,'ON CPU') event,
       NVL(wait_class,'CPU') wait_class,
       COUNT(*) samples,
       ROUND(COUNT(*) * 10 / 60,2) active_minutes
FROM dba_hist_active_sess_history
WHERE sql_id='&sql_id'
GROUP BY NVL(event,'ON CPU'),
         NVL(wait_class,'CPU')
ORDER BY samples DESC;

这一步主要解决一个问题：Elapsed 很高，到底是 SQL 自己在做事，还是大部分时间其实在等。

再通过 AWR 看历史变化

当前数据只能说明这一刻，业务反馈“以前几秒，现在几十秒”可以作为参考，但是建议最好从 AWR 看真实历史：

SET LINES 300
SET PAGES 300

SELECT s.instance_number,
       s.snap_id,
       TO_CHAR(s.begin_interval_time,'YYYY-MM-DD HH24:MI') begin_time,
       st.plan_hash_value,
       st.executions_delta executions,
       st.buffer_gets_delta buffer_gets,
       st.disk_reads_delta disk_reads,
       st.rows_processed_delta rows_processed,
       ROUND(st.disk_reads_delta/
             NULLIF(st.executions_delta,0)) reads_per_exec,
       ROUND(st.buffer_gets_delta/
             NULLIF(st.executions_delta,0)) gets_per_exec,
       ROUND(st.rows_processed_delta/
             NULLIF(st.executions_delta,0)) rows_per_exec,
       ROUND(st.elapsed_time_delta/1e6/
             NULLIF(st.executions_delta,0),2) elapsed_per_exec_s,
       ROUND(st.cpu_time_delta/1e6/
             NULLIF(st.executions_delta,0),2) cpu_per_exec_s
FROM dba_hist_sqlstat st
JOIN dba_hist_snapshot s
  ON s.dbid=st.dbid
 AND s.instance_number=st.instance_number
 AND s.snap_id=st.snap_id
WHERE st.sql_id='&sql_id'
ORDER BY s.begin_interval_time DESC,
         s.instance_number;

这里主要比较 Plan Hash、Reads/Exec、Gets/Exec、Rows/Exec、Elapsed/Exec 和 Executions。

AWR 这一层最重要的是找到：到底哪个指标从什么时候开始发生变化。

确认是 SQL 本身的问题，再看执行计划

如果前面的证据已经指向 SQL 自身访问量或访问路径，再看执行计划：

SET LINES 300
SET PAGES 500
SET LONG 1000000
SET LONGCHUNKSIZE 1000000

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        '&sql_id',
        NULL,
        'ALLSTATS LAST +PEEKED_BINDS +PREDICATE +ALIAS'
    )
);

我主要看：

Access Predicate
Filter Predicate
E-Rows / A-Rows
Starts
Buffers / Reads
Peeked Binds

表面上出现 INDEX RANGE SCAN 不代表访问路径一定合理。真正要看的是 Oracle 用什么条件进入索引，又有哪些条件被留到回表以后才过滤。

E-Rows / A-Rows 也很重要。如果 CBO 估算几十行，实际却返回几十万行，说明基数估算已经明显失真，这时候下一步应该先查 Stats、Histogram、Bind 或列相关性，而不是直接开始建索引。

ALLSTATS LAST 只有在实际执行统计被采集时才能看到完整的 A-Rows、Buffers 等信息。如果没有采集到，不要把缺失值当成 0，先利用现有的 E-Rows、Predicate、Peeked Binds 和其他运行数据继续分析。

根据 Plan，再查 Stats、索引、选择性和 Bind

如果执行计划估算明显不对，先查表统计信息：

SET LINES 300
SET PAGES 200

SELECT owner,
       table_name,
       num_rows,
       blocks,
       avg_row_len,
       stale_stats,
       stattype_locked,
       TO_CHAR(last_analyzed,'YYYY-MM-DD HH24:MI:SS') last_analyzed
FROM dba_tab_statistics
WHERE owner='&owner'
  AND table_name='&table_name'
  AND object_type='TABLE';

重点看 NUM_ROWS、BLOCKS、LAST_ANALYZED、STALE_STATS 和 STATTYPE_LOCKED。如果 CBO 仍然拿着明显过期的数据规模和分布做成本估算，计划出现偏差并不奇怪。

Stats 没明显问题，再把现有索引摊开：

SET LINES 300
SET PAGES 300

SELECT i.index_name,
       i.uniqueness,
       i.status,
       i.degree,
       ic.column_position,
       ic.column_name
FROM dba_indexes i
JOIN dba_ind_columns ic
  ON ic.index_owner=i.owner
 AND ic.index_name=i.index_name
WHERE i.table_owner='&owner'
  AND i.table_name='&table_name'
ORDER BY i.index_name,
         ic.column_position;

再看真正参与过滤的字段：

SELECT column_name,
       num_distinct,
       num_nulls,
       density,
       histogram,
       num_buckets,
       sample_size,
       TO_CHAR(last_analyzed,'YYYY-MM-DD HH24:MI') last_analyzed
FROM dba_tab_col_statistics
WHERE owner='&owner'
  AND table_name='&table_name'
ORDER BY column_name;

这里真正要回答的不是“有没有索引”，而是索引列顺序是否匹配 SQL 的过滤方式，当前 Access Predicate 的选择性到底怎么样。

如果 SQL 使用 Bind Variable，还要把实际捕获到的 Bind 一起看：

SET LINES 300
SET PAGES 300

SELECT inst_id,
       sql_id,
       child_number,
       name,
       position,
       datatype_string,
       value_string,
       TO_CHAR(last_captured,'YYYY-MM-DD HH24:MI:SS') last_captured
FROM gv$sql_bind_capture
WHERE sql_id='&sql_id'
ORDER BY inst_id,
         child_number,
         position;

同一条时间范围 SQL，查一天和查三个月，SQL_ID 可以完全一样，实际扫描量却不是一回事。自己构造测试 SQL 时，Bind 尽量还原业务现场。

到这里，优化方向通常已经比较清楚：Stats 失真就处理统计信息；访问路径不合理再考虑索引；扫描范围本身太大就考虑 SQL 改写、分区裁剪或者业务侧缩小范围；如果 SQL 本身读得不多，真正耗时来自 Blocking、RAC 或 I/O 等待，就应该处理对应等待，而不是继续加索引。

优化后，再跑一次核心指标验收

真正实施变更以后，不需要重新发明一套验收方法，直接回到文章最开始那几个指标：

SELECT inst_id,
       sql_id,
       child_number,
       plan_hash_value,
       executions,
       ROUND(disk_reads/NULLIF(executions,0)) reads_per_exec,
       ROUND(buffer_gets/NULLIF(executions,0)) gets_per_exec,
       ROUND(rows_processed/NULLIF(executions,0)) rows_per_exec,
       ROUND(elapsed_time/1e6/NULLIF(executions,0),2) elapsed_per_exec_s,
       ROUND(cpu_time/1e6/NULLIF(executions,0),2) cpu_per_exec_s,
       TO_CHAR(last_active_time,'YYYY-MM-DD HH24:MI:SS') last_active
FROM gv$sql
WHERE sql_id='&sql_id'
ORDER BY inst_id,child_number;

前后至少对比：

Plan Hash
Reads/Exec
Gets/Exec
Rows/Exec
Elapsed/Exec

例如：

Explain Plan 变好了不算结束，手工测试变快也不能完全代表业务现场。条件允许的话，等真实业务再次执行以后，再从 GV$SQL 或 AWR 验证一次，结果才更有说服力。

最后

如果把整套过程压成一条线，就是：

SQL 优化真正重要的不是最后用了哪一种手段，而是前面的证据链能不能说明：它为什么慢、为什么这样改，以及改完以后到底改善了多少。