Top Sessions
Narrowing down the use of gv$active_session_history to the session deatils, we can pull out the sum of the session states and divide them up by cpu, wait, i/o wait.
set linesize 200
col MACHINE for a40
select * from (
select
--session details group
a.inst_id,
a.session_id sid,
a.session_serial# serial#,
u.username,
a.machine,
--sum the session states
sum(decode(a.session_state,'ON CPU',1,0)) CPU,
sum(decode(a.session_state,'WAITING',1,0)) - sum(decode(a.session_state,'WAITING',decode(wait_class,'User I/O',1, 0 ), 0)) WAIT ,
sum(decode(a.session_state,'WAITING',decode(wait_class,'User I/O',1, 0 ), 0)) IO,
sum(decode(session_state,'ON CPU',1,1)) TOTAL
from gv$active_session_history a, dba_users u
where a.user_id = u.user_id
and a.sample_time > sysdate - 10/24/60 --< in the last n minutes
group by inst_id,session_id,username,session_serial#,machine
order by sum(decode(session_state,'ON CPU',1,1)) desc
) where rownum < 10;
Top SQL
With the same basic query we can transfer our focus to the SQL details of ASH
set linesize 200
select * from (
select
--sql details
a.sql_id ,
a.sql_plan_hash_value phv,
--sum the session states
sum(decode(a.session_state,'ON CPU',1,0)) CPU,
sum(decode(a.session_state,'WAITING',1,0)) - sum(decode(a.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) WAIT,
sum(decode(a.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) IO,
sum(decode(a.session_state,'ON CPU',1,1)) TOTAL
from gv$active_session_history a
where sql_id is not null
and a.sample_time > sysdate - 10/24/60 --< in the last n minutes
group by sql_id, sql_plan_hash_value
order by sum(decode(session_state,'ON CPU',1,1)) desc
) where rownum < 10;
******************************************
keywords: top sessions ash
******************************************
rdbms version: 11g
******************************************
No comments:
Post a Comment