Notice: get_currentuserinfo is deprecated since version 4.5.0! Use wp_get_current_user() instead. in /home/dbascrip/public_html/wp-includes/functions.php on line 3828
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
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",
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.