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.

Description

This query will show you repartition percentage between I/O, WAITS and CPU from the v$active_session_history view
Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it.

Instance activity repartition

select 	(act.WAITING*100/act.TOTAL) as WAITING_PCT,
		(act.CPU*100/act.TOTAL) as CPU_PCT,
		(act.IO*100/act.TOTAL) AS IO_PCT
from
	(select
		 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) act;

This query returns percentage of I/O, CPU and waits consumed by the instance for the total time covered by v$active_session_history. You can specify a time interval by adding a clause in the subquery specifying a range for sample_time.

Description

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 (
	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.

You can see my other post about how to build a graph from the v$active_session_history view here