Average Active Sessions (AAS)
Top 10 queries from v$active_session_history select * from ( select SQL_ID , 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 from v$active_session_history 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. -- Active Sessions (in ASH) including BACKGROUND processes in Last 1 Hour. SET LINESIZE 200 SET PAGESIZE 200 COLUMN sample_time FORMAT a19 COLUMN stat_name FORMAT a15 ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss'...