oracle ASH

http://docs.oracle.com/cd/E11882_01/server.112/e41573/autostat.htm#PFGRF027

ASH以V$SESSION为基础，每秒采样一次，记录活动会话等待的事件。不活动的会话不会采样，采样工作由新引入的后台进程MMNL来完成。
ASH buffers 的最小值为1MB,最大值不超过30MB。内存中记录数据。期望值是记录一小时的内容；若ASH数据被刷新到磁盘，则需要从DBA_HIS_ACTIVE_SESS_HISTORY视图中查询相关信息。

记录最近时间的等待时间，初步调优语句

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

可以生成报告：

生成ASH报告：
SQLPLUS>@?/rdbms/ashrpt.sql

其他：

用法举例：查找最近一分钟内，最消耗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))