如何用Oracle ASH分析过去一小时CPU最高SQL

在 Oracle 运维中，系统突然变慢、CPU 飙升至 100% 是极为常见的紧急场景。快速定位“过去一小时里，究竟是哪条 SQL 语句吃掉了大量 CPU”是 DBA 的核心需求。如果此时慢吞吞地跑 AWR 报告，等报告生成完毕，热点可能早已过去。相比之下，ASH（Active Session History）才是真正的“快速响应部队”，能够在几秒内锁定高 CPU 消耗的 SQL。

如何通过 ASH 视图查询过去一小时内 CPU 消耗最高的 SQL？

核心查询方法非常简单：利用 v$active_session_history 视图。该视图每秒对活跃会话采样一次，其中 session_state 字段会明确标识会话状态。当值为 'ON CPU' 时，表示该会话在采样瞬间正在消耗 CPU。因此，判断一条 SQL 是否为“CPU 大户”，并不取决于它的总执行时长，而是看它在 CPU 上被“捕获”的次数——采样次数越多，CPU 占用强度自然越大。

在实际操作中，有几个细节容易出错，需要特别留意：

• 时间范围应使用 sample_time >= SYSDATE - 1/24，而不是 SYSDATE - INTERVAL '60' MINUTE。后者在部分 Oracle 版本中可能因时区或 NLS 设置导致采样范围偏差，影响数据准确性。
• 聚合分析时，sql_id 是最稳定的键。它比对 sql_text 直接聚合更可靠，因为同一逻辑的 SQL 可能因绑定变量值不同或硬解析导致文本不完全一致。
• 最关键的一步：WHERE session_state = 'ON CPU' 条件绝对不能遗漏。如果不加此条件，会将等待事件（如 I/O 等待、锁等待）的会话一并纳入统计，结果会完全失真。

如何按 CPU 采样次数排序并关联 SQL 文本？

获取采样次数后，下一步自然是查看这些 SQL 的具体内容。直接关联 v$sql 虽然速度较快，但有一个常见陷阱：当 shared pool 压力过大时，SQL 游标可能已被 aged out。因此，更稳妥的做法是关联 dba_hist_sqltext，该视图保存了历史 SQL 文本，只要有 AWR 权限即可查询。

一个典型的查询语句如下：

SELECT s.sql_id,
       COUNT(*) AS cpu_samples,
       t.sql_text
FROM v$active_session_history s
JOIN dba_hist_sqltext t ON s.sql_id = t.sql_id
WHERE s.session_state = 'ON CPU'
  AND s.sample_time >= SYSDATE - 1/24
  AND t.sql_text IS NOT NULL
GROUP BY s.sql_id, t.sql_text
ORDER BY cpu_samples DESC
FETCH FIRST 5 ROWS ONLY;

使用此查询时需要注意几个容易中招的地方：

• 如果发现 sql_text 显示为 /* SQL Analyze(123) */ SELECT ... 等自动任务 SQL，不要急于将其视为问题。需结合 program 或 module 字段确认是否来自 DBMS_SQLTUNE 等维护操作——这类 SQL 通常并非真正的业务瓶颈。
• 对于绑定变量极多、SQL 超长的场景，dba_hist_sqltext 默认只存储前 1000 个字符。如需查看完整内容，可使用 sql_fulltext 字段（Oracle 11g 及之后版本支持）。

为什么查到的 TOP SQL 与 AWR 报告不一致？

这是经典困惑，许多人会遇到。其实两者的统计逻辑完全不同：ASH 是实时的瞬间快照，关注“谁在某一刻吃 CPU”；AWR 是汇总统计，关注“谁在整个快照周期内平均负载更高”。

举例说明：一条 SQL 仅运行 5 秒，但全程 100% 占用 CPU，ASH 会记录 5 次采样；另一条 SQL 运行 30 秒，但大部分时间在等待 I/O，仅最后 2 秒在 CPU 上，ASH 只记录 2 次。从 ASH 角度看，前者更显眼；但从 AWR 角度看，后者因总执行时间长，排名反而可能更高。

判断时可以依据以下经验：

• 如果 ASH 中某个 sql_id 的 cpu_samples 远超其他（例如达到 50 次以上），基本可以确认它就是造成 CPU 瞬时尖峰的源头。
• 接着通过 v$sql 查看 cpu_time / elapsed_time 比值，若接近 100%，说明该 SQL 确实是纯计算型瓶颈，与 I/O 或锁无关。
• 同时留意 sql_plan_hash_value 是否频繁变化。ASH 中可以通过该字段查看执行计划的历史快照，如果它在短时间内跳变多次，计划突变往往是 CPU 飙升的直接导火索。

查到高 CPU SQL 后，下一步该如何处理？

获取目标 sql_id 后，最忌讳直接拍脑袋调优。建议先做几步验证，确认它是否真的是问题根源：

• 使用 DBMS_XPLAN.DISPLAY_CURSOR('xxx', NULL, 'ALLSTATS LAST') 可高效查看该 SQL 上次执行时的实际执行计划。重点关注 Buffers 列是否异常偏高，以及 CPU 列数据是否与采样次数吻合。
• 结合 client_identifier 或 machine 字段，确认该 SQL 是否属于定时跑批任务。如果是计划内的任务，CPU 占用较高也属正常，可能无需干预。
• 分析同一 sql_id 在不同时间段的 cpu_samples 分布，判断它是偶发性尖刺还是持续吃 CPU。后者的处理优先级更高。
• 如果发现 SQL 中包含大量 UNION ALL，或者出现驱动表选择错误的嵌套循环，容易触发 CPU 密集型全表扫描。此时，与其反复查看执行计划，不如直接检查 access_predicates 和 filter_predicates 中的条件，往往能更快锁定问题。

最后提醒一点：ASH 本身无法记录某段 PL/SQL 代码在哪个具体行号上消耗了 CPU。如果需要更细粒度的分析，需借助 DBMS_HPROF 或 oradebug short_stack 进行深入诊断。