Oracle数据库CPU占用过高时如何快速定位消耗资源的SQL语句

定位导致数据库CPU飙高的SQL语句，是每位DBA必须掌握的核心技能。然而，方法不当往往会导致排查方向错误，浪费大量宝贵时间。本文将深入探讨如何精准、高效地定位消耗CPU资源的“元凶”SQL。

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

最直接且高效的方法，是查询 v$active_session_history 视图中 session_state = 'ON CPU' 样本数最多的 sql_id。 其原理非常清晰：Oracle的ASH（活动会话历史）机制每秒对活动会话进行一次采样，每个样本大约代表10毫秒的CPU时间。因此，如果某个SQL被采样到500次，就意味着在采样期间它大约占用了5秒的CPU资源。样本数越高，该SQL消耗CPU的嫌疑就越大。

为何不能仅依赖执行次数或总耗时？

这里需要明确一个关键概念：ASH记录的是“在特定采样时刻，会话正在执行什么操作”，而非SQL的执行次数或累计耗时。因此，高CPU消耗SQL的核心特征并非“执行缓慢”，而是“长时间占据CPU资源”。

举例说明：一个 sql_id 在一小时内被ASH采样到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。问题可能源于硬解析风暴、低效的PL/SQL循环或大量递归调用等非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'))，这是分析SQL性能问题的核心依据。
• 检查是否存在多子游标：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。否则，您手中的 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 历史视图，并指定明确的时间范围。