1.v$active_session_history 獲取當前或最近資料庫中的會話資訊
2.dba_hist_active_sess_history 儲存ash歷史資訊
3.檢視過去15分鐘內資料庫中所有事件以及它們總的等待時間：
select s.event,sum(s.wait_time + s.time_waited) total_wait
from v$active_session_history s
where s.sample_time between sysdate-1/24/4 and sysdate-1/24/4
group by s.event
order by 2 desc;
4.如果想得到更具體的會話資訊，並且想檢視過去15分鐘內使用最多cpu資源的前5位會話，可以提交下面的語句：
select * from
(select s.username,s.module,s.sid,s.serial#,count(*)
from v$active_session_history h,v$session s
where h.session_id = s.sid
and h.session_serial# = s.serial#
and session_state = 'ON CPU' and sample_time > sysdate - interval '15' minute
group by s.username,s.module,s.sid,s.serial#
order by count(*) desc
)
where rownum <= 5;
5.如果想要檢視某個給定取樣週期內使用最多的資料庫物件，則可以將v$active_session_history和dba_objects連線起來獲得資訊：
select * from
(select o.object_name,o.object_type,s.event,sum(s.wait_time + s.time_waited) total_waited
from v$active_session_history s,dba_objects o
where s.sample_time > sysdate - interval '15' minute
and s.current_obj# = o.object_id
group by o.object_name,o.object_type,s.event
order by 4 desc
)
where rownum <= 5;
6.查詢某天使用資源最多的使用者：
select * from
(select u.username,h.module,h.session_id sid,h.session_serial# serial#,count(*)
from dba_hist_active_sess_history h,dba_users u
where h.user_id = u.user_id
and session_state = 'ON CPU'
and (sample_time between to_date('20170101 00:00:00','yyyymmdd hh24:mi:ss') and to_date('20170131 23:59:59','yyyymmdd hh24:mi:ss'))
and u.username != 'SYS'
group by u.username,h.module,h.session_id,h.session_serial#
order by count(*) desc
)
where rownum <= 5;
接下來要關注具體的資料庫物件，可以面向同樣的時間幀提交下面的查詢：
select * from
(select o.object_name,o.object_type,s.event,sum(s.wait_time + s.time_waited) total_waited
from v$active_session_history s,dba_objects o
where (sample_time between to_date('20170101 00:00:00','yyyymmdd hh24:mi:ss') and to_date('20170131 23:59:59','yyyymmdd hh24:mi:ss'))
and s.current_obj# = o.object_id
group by o.object_name,o.object_type,s.event
order by 4 desc
)
where rownum <= 5;

從資料字典中獲取ash資訊

相關文章