使用V$ACTIVE_SESSION_HISTORY视图，查看消耗资源的sql

最新推荐文章于 2025-07-26 10:47:10 发布
最新推荐文章于 2025-07-26 10:47:10 发布
阅读量1k 收藏 2
点赞数
分类专栏： oracle性能优化 oracle故障处理
本文提供了一系列SQL查询语句，用于诊断数据库中过去一分钟内CPU使用率最高、I/O消耗最大以及总体资源消耗最多的SQL语句和会话。通过这些查询，可以快速定位数据库性能瓶颈。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

  1. --查找最近一分钟内，最消耗CPU的sql语句   
  2. SELECT sql_id,   
  3.     count(*),   
  4.     round(count(*) / sum(count(*)) over(), 2) pctload  
  5. FROM V$ACTIVE_SESSION_HISTORY  
  6. WHERE sample_time > sysdate - 1 / (24 * 60)  
  7.     AND session_type <> 'BACKGROUND'  
  8.     AND session_state = 'ON CPU'  
  9. GROUP BY sql_id  
  10. ORDER BY count(*) desc;  
  11.   
  12. --查找最近一分钟内，最消耗I/O的sql语句  
  13. SELECT ash.sql_id,  
  14.     count(*)  
  15. FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EVT  
  16. WHERE ash.sample_time > sysdate -1/(24*60)  
  17.     AND ash.session_state = 'WAITING'  
  18.     AND ash.event_id = evt.event_id  
  19.     AND evt.wait_class = 'USER I/O'  
  20. GROUP BY ash.sql_id  
  21. ORDER BY count(*) desc;  
  22.   
  23. --查找最近一分钟内，最消耗CPU的session  
  24. SELECT session_id,  
  25.     count(*)  
  26.     FROM V$ACTIVE_SESSION_HISTORY  
  27. WHERE session_state = 'ON CPU'  
  28.     AND sample_time > sysdate -1/(24*60)  
  29. GROUP BY session_id  
  30. ORDER BY count(*) desc;  
  31.   
  32. --查找最近一分钟内，最消耗资源的sql语句  
  33. SELECT ash.sql_id,  
  34.     sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",  
  35.     sum(decode(ash.session_state,'WAITING',1,0)) -  
  36.     sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "WAIT",  
  37.     sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "IO",  
  38.     sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"  
  39.     FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EN  
  40. WHERE SQL_ID is not null and en.event#=ash.event# and ash.sample_time > sysdate -1/(24*60)  
  41. GROUP BY ash.sql_id  
  42. ORDER BY sum(decode(ash.session_state,'ON CPU',1,1)) desc;  
  43.   
  44. --查找最近一分钟内，最消耗资源的session  
  45. SELECT ash.session_id,  
  46.     ash.session_serial#,  
  47.     ash.user_id,  
  48.     ash.program,  
  49.     sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",  
  50.     sum(decode(ash.session_state,'WAITING',1,0)) -  
  51.     sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "WAITING",  
  52.     sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "IO",  
  53.     sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"  
  54. FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EN  
  55. WHERE en.event# = ash.event# and ash.sample_time > sysdate -1/(24*60)  
  56. GROUP BY ash.session_id,ash.user_id,ash.session_serial#,ash.program  
  57. ORDER BY sum(decode(ash.session_state,'ON CPU',1,1));  
oracle学习-V$ACTIVE_SESSION_HISTORY 视图-官方文档解释
weixin_33991418的博客
11-30 5811
2019独角兽企业重金招聘Python工程师标准>>> ...
oracle 257,ORACLE 10g SYSAUX表空间快速增长之WRH$_ACTIVE_SESSION_HISTORY篇
weixin_36400512的博客
04-11 988
近一段时间监控生产数据库的表空间使用情况，发现SYSAUX表空间非常大(17g之多)，而且每天都有几十M的增长，看到此现象后，认为这很不正常，分析SYSAUX表空间那些segments占用空间比较大：SELECT SUM(T.BYTES)/1024/1024 segments_SIZE,T.segment_nameFROM Dba_Segments tWHERE t.tablespace_name...
gv$active_session_history
喝醉酒的小白
11-23 2897
select session_id, session_type, sql_opname, sql_id from gv$active_session_history where sample_time between to_date('20211122 17:00:00', 'YYYYMMDD HH24:MI:SS') and to_date('20211122 17:01:00', 'YYYYMMDD HH24:MI:SS'); select sql_id, sql_text from v$sql
Oracle 的V$ACTIVE_SESSION_HISTORY 视图
文牧之的博客
05-22 705
它包含每秒拍摄一次的活动数据库会话的快照。如果数据库会话位于 CPU 上或正在等待不属于等待类的事件，则认为该会话处于活动状态。请参阅 view 以了解有关 wait classes 的更多信息。此视图为每个样本的每个活动会话包含一行，并首先返回最新的会话样本行。视图中显示活动会话历史记录中描述会话的大多数列。V$ACTIVE_SESSION_HISTORY显示数据库中的 Sampled Session 活动。
11g v$active_session_history的新增列
cti27515的博客
10-03 358
很多知识我都还停留在10g，今天同事在ASH中直接就找到了SQL语句消耗的PGA，才发现，11gR2的ASH多了如此多的列1.标示这条ASH记录是否被刷入了磁盘 IS_AWR_SAMPLE ...
转载--V$ACTIVE_SESSION_HISTORY视图的使用
热门推荐
还不算晕的专栏
01-07 1万+
V$ACTIVE_SESSION_HISTORY 显示数据库中的采样会话活动。ASH每秒从v$session中取快照，存在V$ACTIVE_SESSION_HISTORY中，并收集所有活动会话的等待信息。若ASH数据被刷新到磁盘，则需要从DBA_HIS_ACTIVE_SESS_HISTORY视图中查询相关信息。 该视图是ASH的核心，用以记录活动SESSION的历史等待信息，每秒采样一次，这
V $ ACTIVE_SESSION_HISTORY
allway2的博客
11-27 3437
V$ACTIVE_SESSION_HISTORY显示数据库中的采样会话活动。它包含每秒拍摄一次的活动数据库会话的快照。如果数据库会话在CPU上或正在等待不属于Idlewait类的事件，则认为该数据库会话处于活动状态。请参阅该V$EVENT_NAME视图以获取有关等待类的更多信息。 该视图为每个样本的每个活动会话包含一行，并首先返回最新的会话样本行。V$SESSION视图中显示活动会话历史记录中描述会话的大多数列。 柱 数据类型 描述 SAMPLE_ID NUMBER 样品编
一次WRH$_ACTIVE_SESSION_HISTORY问题处理
ciyilv0512的博客
10-25 3344
前几天处理了一次Oracle高可用变成不可用的问题。问题出在这个上面WRH$_ACTIVE_SESSION_HISTORY。 环境是有一个RAC和一个单实例数据库的背景。先是单实例数据库在我抽查AWR的时候发现很糟糕。（我不是...
Oracle三大性能视图v$session到ASH，分析视图数据来源、保留时间和用法！
Jackyimi的博客
07-26 990
本文介绍了Oracle数据库中的三种性能视图及其应用场景：v$session（实时会话状态）、v$active_session_history（每秒采样的活动会话内存数据）和dba_hist_active_sess_history（持久化存储的AWR历史数据）。重点分析了各视图的更新频率（每秒采样、每小时持久化）和数据保留时间（内存中几分钟、磁盘默认8天），并提供了生产环境的使用建议。文章还说明了ASH缓冲区大小查看方法及调整_ash_size参数的注意事项，强调需谨慎修改隐含参数。
oracle 11g v$sql,Oracle 11g实时SQL监控 v$sql_monitor
weixin_39661589的博客
04-08 1072
Oracle 11g实时SQL监控：前面提到，在OracleDatabase11g中，v$session视图增加了一些新的字段，这其中包括SQL_EXEC_START和SQL_EXEC_ID，这两个字段实际上代表了Oracle 11g的一个新特性：实时的SQL监控(RealTime SQL Monitoring)。在Oracle 11g之前的版本，长时间运行的SQL可以通过监控v$session_...
V$ACTIVE_SESSION_HISTORY视图的列说明
放飞的心灵－记录学习的点点滴滴
06-21 2744
V$ACTIVE_SESSION_HISTORY视图的列说明 V$ACTIVE_SESSION_HISTORY 显示数据库中的采样会话活动。ASH每秒从v$session中取快照，存在V$ACTIVE_SESSION_HISTORY中，并收集所有活动会话的等待信息。若ASH数据被刷新到磁盘，则需要从DBA_HIS_ACTIVE_SESS_HISTORY视图中查询相关信息。 该视图是ASH的核心
oracle查询active线程,查询v$active_session_history视图后的疑问
weixin_34582773的博客
04-07 537
小弟最近在优化一个大sql，在sql执行期间通过v$active_session_history视图查询到如下结果：EVENT CURRENT_OBJ# COUNT(*)--------------------------------------------- ------------ ----------db f...
V$ACTIVE_SESSION_HISTORY 显示数据库中的采样会话活动
menghuimp的博客
10-14 646
V$ACTIVE_SESSION_HISTORY 显示数据库中的采样会话活动。ASH每秒从v$session中取快照，存在V$ACTIVE_SESSION_HISTORY中，并收集所有活动会话的等待信息。若ASH数据被刷新到磁盘，则需要从DBA_HIS_ACTIVE_SESS_HISTORY视图中查询相关信息。 该视图是ASH的核心，用以记录活动SESSION的历史等待信息，每秒采样一次，这部分
Oracle 故障处理|V$ACTIVE_SESSION_HISTORY 视图没有数据?
JiekeXu的博客
03-09 1965
作者 | JiekeXu来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）如需转载请联系授权 |(个人微信 ID：JiekeXu_DBA)大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 Oracle 故障处理|V$ACTIVE_SESSION_HISTORY 视图没有数据?，欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶...
利用v$active_session_history跟踪会话
cqcj2023的博客
06-02 524
To be fair to the 10046 trace, the v$active_session_history does not catch session activity that is e...
Active Session History (ASH) 读书笔记
In-Memory Computing Technology
10-14 471
ASH（Active Session History）读书笔记
oracle学习-DBA_HIST_ACTIVE_SESS_HISTORY
weixin_34220834的博客
11-30 1225
2019独角兽企业重金招聘Python工程师标准>>> ...
SELECT s.machine, s.program, s.module, ash.sql_id, ash.sql_exec_id FROM v$active_session_history ash JOIN v$session s ON ash.session_id = s.sid WHERE ash.sql_id = 'c8yts9cgbyb52'; 讓這個sql查詢 添加sql文本內容，
最新发布
08-10
<think>我们有两个视图：v$session 和 v$active_session_history。用户想要在查询这些视图时同时显示SQL的文本内容。 在Oracle中，SQL文本存储在v$sql或v$sqlarea等视图中。我们可以通过SQL_ID（在v$session和v$active_session_history中都有）来关联这些视图，从而获取SQL文本。 注意：v$active_session_history中存储的是历史活动会话信息，而v$session是当前会话信息。 对于v$session，我们可以这样写： SELECT s.sid, s.serial#, s.username, s.machine, s.program, s.module, s.client_info, q.sql_text FROM v$session s LEFT JOIN v$sql q ON s.sql_id = q.sql_id WHERE s.status = 'ACTIVE'; 但是注意，一个SQL_ID可能对应多个子游标（child cursor），但通常我们取其中一个即可（比如通过sql_id和child_number），或者使用v$sqlarea（它按sql_id聚合，但只显示一个子游标的sql_text）。不过，v$sqlarea中sql_text可能是截断的（只显示前1000字符），而v$sql中sql_fulltext是完整的（CLOB类型，但显示时可能受限于工具）。 另外，v$active_session_history中，每个活动会话每秒采样一次，可能同一个会话在多次采样中执行不同的SQL，所以我们需要根据SQL_ID来关联。 对于v$active_session_history的查询： SELECT ash.sample_id, ash.sample_time, ash.session_id, ash.session_serial#, ash.sql_id, ash.sql_exec_id, ash.event, ash.wait_class, q.sql_text FROM v$active_session_history ash LEFT JOIN v$sql q ON ash.sql_id = q.sql_id WHERE ash.sample_time BETWEEN ... AND ...; 但是，v$active_session_history中可能包含历史数据，而v$sql中可能已经没有对应的SQL（因为SQL游标被刷出共享池）。为了获取更久远的SQL文本，我们可以关联dba_hist_sqltext（需要AWR许可，并且已经做过快照）。 因此，如果我们想要查询v$active_session_history并且显示SQL文本，有两种情况： 1. 如果SQL还在共享池中（v$sql中还存在），则从v$sql中获取。 2. 如果SQL已经不在共享池中，则从dba_hist_sqltext中获取（前提是AWR快照已经捕获）。 但是，由于用户没有特别说明历史数据，我们先提供当前共享池中能关联到的SQL文本。 另外，注意v$sql中的sql_text是前1000个字符，而sql_fulltext是完整的SQL文本（CLOB）。我们可以使用DBMS_LOB.SUBSTR来截取，或者根据工具支持直接显示。 以下提供两个查询示例： 1. 查询v$session并显示SQL文本（当前正在执行的SQL）： SELECT s.sid, s.serial#, s.username, s.machine, s.program, s.module, s.client_info, s.sql_id, q.sql_text, -- 前1000个字符 DBMS_LOB.SUBSTR(q.sql_fulltext, 4000, 1) AS sql_fulltext -- 显示最多4000字符 FROM v$session s LEFT JOIN v$sql q ON s.sql_id = q.sql_id WHERE s.status = 'ACTIVE'; 2. 查询v$active_session_history并显示SQL文本（注意：v$active_session_history中的SQL_ID可能为null，表示当时没有执行SQL）： SELECT ash.sample_id, ash.sample_time, ash.session_id, ash.session_serial#, ash.sql_id, ash.sql_exec_id, ash.event, ash.wait_class, q.sql_text, DBMS_LOB.SUBSTR(q.sql_fulltext, 4000, 1) AS sql_fulltext FROM v$active_session_history ash LEFT JOIN v$sql q ON ash.sql_id = q.sql_id WHERE ash.sample_time > SYSDATE - INTERVAL '1' HOUR -- 最近一小时 ORDER BY ash.sample_time DESC; 但是，如果v$sql中已经没有了，我们可以尝试关联dba_hist_sqltext（需要AWR许可）： SELECT ash.sample_id, ash.sample_time, ash.session_id, ash.session_serial#, ash.sql_id, ash.sql_exec_id, ash.event, ash.wait_class, COALESCE(q.sql_text, h.sql_text) AS sql_text FROM v$active_session_history ash LEFT JOIN v$sql q ON ash.sql_id = q.sql_id LEFT JOIN dba_hist_sqltext h ON ash.sql_id = h.sql_id WHERE ash.sample_time > SYSDATE - INTERVAL '1' HOUR ORDER BY ash.sample_time DESC; 注意：dba_hist_sqltext中的sql_text是完整的SQL文本（VARCHAR2类型，最多4000字符），如果SQL文本超过4000字符，则会被截断。而v$sql中的sql_fulltext是CLOB，可以显示更长的文本（但我们在查询时通过DBMS_LOB.SUBSTR转换为4000字符）。 另外，如果用户没有AWR许可，则不能使用dba_hist_sqltext。 因此，我们给出一个通用的，只使用v$sql的查询。 最后，注意权限：用户需要被授予SELECT_CATALOG_ROLE，或者直接授予对v$session, v$sql, v$active_session_history, dba_hist_sqltext的查询权限。 由于用户的问题中提到了“现有的Oracle性能视图查询”，我们假设用户已经有查询这些视图的权限。 现在，根据用户提供的引用，我们知道v$active_session_history是内存中的，重启会消失，而dba_hist_active_sess_history是持久化的。但是，我们这里只需要关联SQL文本，所以用dba_hist_sqltext就可以。 总结：在查询v$session和v$active_session_history时，通过关联v$sql或dba_hist_sqltext来获取SQL文本。</think>### Oracle 查询添加 SQL 文本内容的方法 在 Oracle 中，可以通过关联 `v$sql` 或 `dba_hist_sqltext` 视图来获取 SQL 文本内容。以下是具体实现方法： #### 1. 查询 `v$active_session_history` + SQL 文本 ```sql SELECT ash.sample_id, ash.sample_time, ash.session_id, ash.sql_id, ash.event, ash.wait_class, ash.machine AS "客户端主机", ash.program AS "客户端程序", s.sql_text AS "SQL文本", DBMS_LOB.SUBSTR(s.sql_fulltext, 4000, 1) AS "完整SQL文本" FROM v$active_session_history ash LEFT JOIN v$sql s ON ash.sql_id = s.sql_id WHERE ash.sample_time > SYSDATE - INTERVAL '1' HOUR AND ash.session_state = 'ON CPU' -- 过滤活动会话 ORDER BY ash.sample_time DESC; ``` #### 2. 查询 `v$session` + SQL 文本（实时会话） ```sql SELECT s.sid, s.serial#, s.username, s.machine AS "客户端主机", s.program AS "客户端程序", s.module AS "应用模块", q.sql_text AS "当前SQL文本", DBMS_LOB.SUBSTR(q.sql_fulltext, 4000, 1) AS "完整SQL文本" FROM v$session s LEFT JOIN v$sql q ON s.sql_id = q.sql_id WHERE s.status = 'ACTIVE'; ``` #### 3. 历史 SQL 文本查询（需 AWR 许可） ```sql SELECT ash.sample_time, ash.session_id, ash.sql_id, ash.machine, t.sql_text AS "历史SQL文本" FROM dba_hist_active_sess_history ash JOIN dba_hist_sqltext t ON ash.sql_id = t.sql_id WHERE ash.sample_time BETWEEN TO_DATE('2023-01-01','YYYY-MM-DD') AND TO_DATE('2023-01-02','YYYY-MM-DD'); ``` ### 关键说明： 1. **SQL文本获取原理** - 通过 `sql_id` 关联 `v$sql` 或 `dba_hist_sqltext` 视图[^1][^3] - `v$sql.sql_text`：显示前 1000 字符 - `DBMS_LOB.SUBSTR(sql_fulltext)`：显示完整 SQL（CLOB 转 VARCHAR2） 2. **性能优化建议** ```sql -- 创建 SQL_ID 索引加速关联查询 CREATE INDEX idx_sql_id ON v$sql(sql_id); ``` 3. **权限要求**： ```sql GRANT SELECT ON v_$sql TO your_user; GRANT SELECT ON dba_hist_sqltext TO your_user; ``` ### 注意事项： 1. `v$active_session_history` 数据保留约 1 小时（内存中），重启后丢失[^1][^2] 2. `dba_hist_sqltext` 需要 AWR 许可且已启用快照[^2][^3] 3. 超过 1000 字符的 SQL 需使用 `sql_fulltext` 字段 4. 空闲会话（IDLE）不会出现在 ASH 采样中[^4][^5] > **示例输出**： > ![查询结果示例](https://example.com/oracle-ash-sqltext.png) > *显示客户端主机、程序和对应 SQL 文本的查询结果* ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值