V$ACTIVE_SESSION_HISTORY 显示数据库中的采样会话活动。ASH每秒从v$session中取快照，存在V$ACTIVE_SESSION_HISTORY中，并收集所有活动会话的等待信息。若ASH数据被刷新到磁盘，则需要从DBA_HIS_ACTIVE_SESS_HISTORY视图中查询相关信息。
该视图是ASH的核心，用以记录活动SESSION的历史等待信息，每秒采样一次，这部分内容记录在内存中，期望值是记录一个小时的内容。
用法举例：查找最近一分钟内，最消耗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))
———————
在数据库出现性能问题的时候使用awr,ash,addm都是不错的选择，实际上直接查询v$active_session_history也能很快定位解决问题。
实际上如果查看v$active_session_history视图，结合一些视图可以获取许多信息。
举几个例子来说明:
1.确定那个对象有高的等待:
SELECT a.current_obj#, o.object_name, o.object_type, a.event, SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, dba_objects o
WHERE a.sample_time BETWEEN SYSDATE – 30 / 1440 AND SYSDATE AND a.current_obj# = o.object_id
GROUP BY a.current_obj#, o.object_name, o.object_type, a.event
ORDER BY total_wait_time desc ;
2.看看一段时间主要是那些等待事件：
SELECT a.event, SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a
WHERE a.sample_time BETWEEN SYSDATE – 30 / 1440 AND SYSDATE
GROUP BY a.event
ORDER BY total_wait_time DESC;
3.看看那个回话有问题:
SELECT s.SID, s.username, SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, v$session s
WHERE a.sample_time BETWEEN SYSDATE – 30 / 1440 AND SYSDATE AND a.session_id = s.SID
GROUP BY s.SID, s.username
ORDER BY total_wait_time DESC;
–当然这个只能查询最近的会准一点，回话退出就不行了。
4.看看那个sql语句有问题。
SELECT a.user_id, d.username, s.sql_text, SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, v$sqlarea s, dba_users d
WHERE a.sample_time BETWEEN SYSDATE – 15 / 1440 AND SYSDATE AND a.sql_id = s.sql_id AND a.user_id = d.user_id
GROUP BY a.user_id, s.sql_text, d.username
order by SUM (a.wait_time + a.time_waited) desc
— 这里查询的是v$sqlarea视图。
同样你可以使用视图DBA_HIST_ACTIVE_SESS_HISTORY代替v$active_session_history查询历史的信息。
select * from dba_objects where wner='SYS' and object_name like 'DBA_HIST%' and object_type='VIEW';
利用这些视图定位许多信息问题
