Latest posts by Cyrille Modiano (see all)
- OCP 12C – Resource Manager and Performance Enhancements - 24/10/2014
- OCP 12C – SQL Enhancements - 23/10/2014
- OCP 12C – DataPump, SQL*Loader, External Tables Enhancements - 22/10/2014
This query returns the top 10 queries by resource consumption (CPU+IO+WAIT) in the last hour from v$active_session_history.
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 queries from v$active_session_history
select * from (
sum(decode(session_state,'ON CPU',1,0)) as CPU,
sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT,
sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO,
sum(decode(session_state,'ON CPU',1,1)) as TOTAL
where SQL_ID is not NULL
group by sql_id
order by sum(decode(session_state,'ON CPU',1,1)) desc
where rownum <11
This query return top queries by resources consumed, you can easily return the top I/O, WAITS or CPU queries by changing the order by clause.
You can see my other post about how to build a graph from the v$active_session_history view here