如何通过Oracle ASH分析过去一小时内消耗CPU最高的SQL语句

<p>在ASH中查过去一小时高CPU SQL需查询v$active_session_history，筛选session_state = 'ON CPU'且sample_time >= SYSDATE - 1/24的记录，按sql_id统计采样次数并关联dba_hist_sqltext获取语句，以cpu_samples降序排序。</p>

ASH视图里怎么查过去一小时的高CPU SQL？

直接查 v$active_session_history，它每秒采样一次活跃会话，其中 session_state = 'on cpu' 的记录就是正在消耗cpu的瞬间。关键不是“总执行时间”，而是“被采样到on cpu状态的次数”——这个频次直接反映cpu占用强度。

实操建议：

• 用 sample_time >= SYSDATE - 1/24 限定过去一小时（注意：不是 SYSDATE - INTERVAL '60' MINUTE，后者在某些Oracle版本中可能因时区或NLS设置导致范围偏差）
• sql_id 是聚合粒度最合适的字段，比 sql_text 稳定（避免硬解析/绑定变量差异干扰）
• 别漏加 WHERE session_state = 'ON CPU'，否则会混入等待事件数据，结果失真

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

单纯统计 sql_id 出现次数就能反映CPU消耗排名，但必须关联 v$sql 才能看到真实语句。注意 v$sql 可能已老化（shared pool压力大时），所以优先用 dba_hist_sqltext 回溯历史SQL。

典型查询结构：

SELECT s.sql_id,       COUNT(*) AS cpu_samples,       t.sql_textFROM v$active_session_history sJOIN dba_hist_sqltext t ON s.sql_id = t.sql_idWHERE s.session_state = 'ON CPU'  AND s.sample_time >= SYSDATE - 1/24  AND t.sql_text IS NOT NULLGROUP BY s.sql_id, t.sql_textORDER BY cpu_samples DESCFETCH FIRST 5 ROWS ONLY;

常见坑：

• v$sql 里查不到SQL？说明它已被aged out，必须切到 dba_hist_sqltext（需有AWR权限）
• 如果 sql_text 显示为 /* SQL Analyze(123) */ SELECT ... 这类自动任务SQL，要结合 program 或 module 字段判断是否来自DBMS_SQLTUNE等维护操作
• 某些SQL因绑定变量多、文本超长，dba_hist_sqltext 中只存前1000字符，需用 sql_fulltext 字段（Oracle 11g+）获取完整内容

为什么看到的TOP SQL和AWR报告不一致？

ASH是实时采样快照，AWR是汇总统计，两者逻辑不同：ASH反映“谁在某一刻吃CPU”，AWR反映“谁在整个快照周期内平均负载高”。比如一条SQL执行5秒全占CPU，会被ASH采样5次；另一条执行30秒但只有最后2秒在CPU上，ASH只记2次——但它在AWR里可能因总时间长排更高。

判断依据：

• 若ASH中某 sql_id 的 cpu_samples 远高于其他（比如50+次），基本可确认是瞬时CPU尖峰源头
• 若该SQL在 v$sql 中 cpu_time / elapsed_time 接近100%，说明确实是纯计算型瓶颈，不是I/O或锁拖慢
• 检查对应SQL的 plan_hash_value 是否频繁变化（ASH里可通过 sql_plan_hash_value 字段看），计划突变常导致CPU飙升

查到高CPU SQL后下一步该做什么？

拿到 sql_id 后别急着调优，先验证是否真实问题：

• 用 DBMS_XPLAN.DISPLAY_CURSOR('xxx', NULL, 'ALLSTATS LAST') 看上次执行的实际执行计划，重点关注 Buffers 和 CPU 列是否异常高
• 检查该SQL是否在跑批处理（看 client_identifier 或 machine 字段），如果是定时任务，可能无需干预
• 对比同一 sql_id 在不同时间段的 cpu_samples 分布，确认是否偶发还是持续发生
• 如果SQL含大量 UNION ALL 或嵌套循环，且驱动表选择错误，容易触发CPU密集型全表扫描——这时看 access_predicates 和 filter_predicates 比看执行计划更直接

ASH本身不保存执行上下文细节，比如具体哪一行PL/SQL代码在耗CPU，这种场景得结合 DBMS_HPROF 或 oradebug short_stack 进一步下钻。