使用V$ACTIVE_SESSION_HISTORY视图,查看消耗资源的sql
来源:互联网 发布:常用端口 编辑:程序博客网 时间:2024/06/08 17:36
- --查找最近一分钟内,最消耗CPU的sql语句
- SELECT sql_id,
- count(*),
- round(count(*) / sum(count(*)) over(), 2) pctload
- FROM V$ACTIVE_SESSION_HISTORY
- WHERE sample_time > sysdate - 1 / (24 * 60)
- AND session_type <> 'BACKGROUND'
- AND session_state = 'ON CPU'
- GROUP BY sql_id
- ORDER BY count(*) desc;
- --查找最近一分钟内,最消耗I/O的sql语句
- SELECT ash.sql_id,
- count(*)
- FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EVT
- WHERE ash.sample_time > sysdate -1/(24*60)
- AND ash.session_state = 'WAITING'
- AND ash.event_id = evt.event_id
- AND evt.wait_class = 'USER I/O'
- GROUP BY ash.sql_id
- ORDER BY count(*) desc;
- --查找最近一分钟内,最消耗CPU的session
- SELECT session_id,
- count(*)
- FROM V$ACTIVE_SESSION_HISTORY
- WHERE session_state = 'ON CPU'
- AND sample_time > sysdate -1/(24*60)
- GROUP BY session_id
- ORDER BY count(*) desc;
- --查找最近一分钟内,最消耗资源的sql语句
- SELECT ash.sql_id,
- sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
- sum(decode(ash.session_state,'WAITING',1,0)) -
- sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "WAIT",
- sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "IO",
- sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
- FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EN
- WHERE SQL_ID is not null and en.event#=ash.event# and ash.sample_time > sysdate -1/(24*60)
- GROUP BY ash.sql_id
- ORDER BY sum(decode(ash.session_state,'ON CPU',1,1)) desc;
- --查找最近一分钟内,最消耗资源的session
- SELECT ash.session_id,
- ash.session_serial#,
- ash.user_id,
- ash.program,
- sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
- sum(decode(ash.session_state,'WAITING',1,0)) -
- sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "WAITING",
- sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "IO",
- sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
- FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EN
- WHERE en.event# = ash.event# and ash.sample_time > sysdate -1/(24*60)
- GROUP BY ash.session_id,ash.user_id,ash.session_serial#,ash.program
- ORDER BY sum(decode(ash.session_state,'ON CPU',1,1));
0 0
- 使用V$ACTIVE_SESSION_HISTORY视图,查看消耗资源的sql
- 使用V$ACTIVE_SESSION_HISTORY视图,查看消耗资源的sql
- V$ACTIVE_SESSION_HISTORY视图的使用
- V$ACTIVE_SESSION_HISTORY视图的使用
- 转载--V$ACTIVE_SESSION_HISTORY视图的使用
- ASH的信息通过视图v$active_session_history查看
- V$ACTIVE_SESSION_HISTORY视图的列说明
- V$ACTIVE_SESSION_HISTORY视图的列说明
- oracle sql资源消耗相关视图:v$sql,v$sqlarea,v$sqltext
- v$active_session_history
- v$active_session_history
- 11g v$active_session_history的列说明
- 查看数据库资源的消耗情况
- 如何定位资源消耗多的SQL
- 定位高消耗资源的sql语句
- 查找消耗资源多的sql
- 视图 v$sql,v$sqlarea,$sqltext,v$sqltext_with_newlines 的差异
- v$sql视图和v$sqlarea视图的构建
- 在学习java的路上
- Alamofire 入门教程(下)
- 淘宝内部分享:MySQL & MariaDB性能优化
- HDU1251统计难题(字符串处理)
- ASP.NET连接Oracle
- 使用V$ACTIVE_SESSION_HISTORY视图,查看消耗资源的sql
- 如何使用 awk 和正则表达式过滤文本或文件中的字符串
- tomcat启动异常(严重: Dispatcher initialization failed Unable to load configuration. - [unknown location] )
- Java并发编程:线程间协作的两种方式:wait、notify、notifyAll和Condition
- 一些php魔术方法;
- 音视频封装格式、编码格式知识
- 字符数组及字符串 是否添加'\0'
- 多线程在C、Win32和MFC下的使用方法
- solr客户端查询语法