OCP Oracle DBA in Montreal at Desjardins
Latest posts by Cyrille Modiano (see all)
- Renaming a RAC cluster - 27/09/2018
- Stop/Start all RAC databases at once - 26/09/2018
- RMAN Backup script - 08/11/2017
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.
For more accurately we can also use wait_time, time_waited, tm_delta_time, tm_delta_cpu_time, tm_delta_db_time (tm-since 11.2)
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