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.