Top 10 sessions from v$active_session_history

Cyrille Modiano
Latest posts by Cyrille Modiano (see all)

Description

This query returns the top 10 sessions from v$active_session_history. The result is ordered by total resources consumed by the session including I/O, WAITS and CPU
Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it.

top 10 sessions from v$active_session_history

select * from (
select
     session_id,
	 session_serial#,
     program,
	 module,
	 action,
     sum(decode(session_state,'WAITING',0,1)) "CPU",
     sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING',decode(wait_class,'User I/O',1,0),0)) "WAITING" ,
     sum(decode(session_state,'WAITING',decode(wait_class,'User I/O',1,0),0)) "IO" ,
     sum(decode(session_state,'WAITING',1,1)) "TOTAL"
from v$active_session_history 
where session_type='FOREGROUND'
group by session_id,session_serial#,module,action,program
order by sum(decode(session_state,'WAITING',1,1)) desc)
where rownum <11

You can restrict period of time analyzed by filtering with the sample_time column.

2 thoughts on “Top 10 sessions from v$active_session_history

    1. Hi Sayan,

      Thanks you for reading my blog and for pointing this out.
      You’re right, by using a count we only get an approximation of the time waited as it can happen many things between each one second sample. But even if it is not an exact value it is most of the time sufficient to identify a problem. For further investigation I always look at the AWR report and if needed to a trace on the sql statements involved in the problem identified.

      Cyrille

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.