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;

ITPub博客

从数据字典中获取ash信息

最新文章