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

本文提供了一系列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));  
<think>我们有两个视图:v$sessionv$active_session_history。用户想要在查询这些视图时同时显示SQL的文本内容。 在Oracle中,SQL文本存储在v$sql或v$sqlarea等视图中。我们可以通过SQL_ID(在v$sessionv$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$sqlsql_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$sessionv$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、付费专栏及课程。

余额充值