利用Oracle ASH定位CPU 100%问题：高频SQL_ID排查指南

定位导致数据库CPU飙高的SQL，是每位DBA必须掌握的技能。若方法不当，极易在错误方向上耗费大量时间。本文将探讨如何精准、高效地找出消耗CPU资源的SQL语句。

最直接有效的方法，是查询 v$active_session_history 视图中 session_state = 'ON CPU' 样本数最多的 sql_id。 其逻辑非常直观：ASH（Active Session History）每秒对活动会话进行一次采样，一个样本约代表10毫秒的CPU时间。若某SQL的样本数达到500，则意味着它在采样期间占用了约5秒的CPU资源。样本数越高，嫌疑越大。

长期稳定更新的攒劲资源： >>>点此立即查看<<<

为什么不能只看执行次数或执行时间？

这里需要厘清一个关键概念：ASH记录的不是SQL的执行次数或总耗时，而是“在特定瞬间，会话正在做什么”。因此，高CPU消耗SQL的核心特征并非“执行慢”，而是“长时间占用CPU”。

举例来说：一个 sql_id 在一小时内被采样到1200次，且状态均为 ON CPU，基本可断定它是CPU消耗大户。而另一个SQL虽然总执行时间长达10分钟，但采样中仅有10次为 ON CPU（其余时间可能在等待I/O或锁），其实际CPU占用反而很低。

• session_state = 'ON CPU' 是识别CPU活动的黄金标准，在 wait_class 中并无“CPU”这一分类。
• 不要盲目相信 v$sql.elapsed_time（总耗时）或 executions（执行次数），这些是历史累计值，对定位突发的CPU尖峰帮助有限。
• 同一个 sql_id 可能对应多个子游标（sql_child_number），不同子游标的执行计划可能差异巨大。因此，分析时必须关联查看 sql_plan_hash_value。

如何编写查询语句以准确锁定目标？

以下查询可从内存中实时抓取最近5分钟的CPU样本分布，帮助快速定位问题SQL：

SELECT sql_id, COUNT(*) cpu_samples, MAX(sql_plan_hash_value) plan_hash
FROM v$active_session_history
WHERE session_state = 'ON CPU'
  AND sample_time > SYSDATE - INTERVAL '5' MINUTE
GROUP BY sql_id
ORDER BY cpu_samples DESC
FETCH FIRST 5 ROWS ONLY;

• 过滤条件不可省略：session_state = 'ON CPU' 是核心，遗漏此条件会导致结果混入大量等待事件数据，失去焦点。
• 时间窗口的选择：建议从5分钟开始查询。时间过短（如1分钟）可能因采样波动导致误判；时间过长（如1小时）则可能让低频但高强度的CPU尖峰被平均数据淹没。
• 结果为空的情况：若查询返回空结果，不一定代表没有高CPU SQL。问题可能不在SQL层面，而是由硬解析风暴、低效的PL/SQL循环或大量递归调用引起。此时，需要切换到 event 维度进行排查。

获取 sql_id 后的三个必要步骤

获取 sql_id 仅是第一步。在Oracle中，同一ID背后可能存在完全不同的情况。接下来，必须立即进行以下三重验证：

• 查看SQL文本：SELECT sql_text FROM v$sql WHERE sql_id = '&sql_id' AND ROWNUM <= 3（添加 ROWNUM 是为了防止因SQL文本过长导致查询卡住）。
• 获取当前执行计划：SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'))，这是分析性能问题的关键。
• 检查是否存在多子游标：SELECT sql_id, child_number, plan_hash_value, executions, is_bind_sensitive, is_shareable FROM v$sql WHERE sql_id = '&sql_id'。需特别关注 is_bind_sensitive = 'Y' 的子游标，它们可能因绑定变量窥视而导致执行计划不稳定。

此处有一个需要注意的陷阱：若从 v$sql 中查不到该SQL的文本，说明它可能因LRU机制已从共享池中被老化清除。此时只能尝试从 dba_hist_sqltext 中回溯，但这依赖于AWR快照是否曾捕获过它。否则，手中的 sql_id 将成为一个无从下手的“幽灵”。

两个容易被忽略的关键点

第一，关于数据留存时间。v$active_session_history 是内存中的循环缓冲区，默认仅保留约最近1小时的数据，超时即被覆盖。若刚发现CPU 100%却查不到高样本SQL，很可能是因为问题发生时间早于ASH的保留窗口，数据已被刷新。这在负载较低的实例上尤为常见，缓冲区可能在几分钟内就被新数据填满。

第二，关于资源字段的可靠性。v$active_session_history 中虽然存在 pga_allocated、temp_space_allocated 等字段，但在某些数据库版本或未打特定补丁的情况下，其值可能为空或不稳定。如需精确分析PGA或临时表空间的历史消耗，更可靠的做法是查询 dba_hist_active_sess_history 并指定明确的时间范围。