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.