10.2.0.5 & 11.2.0.2 on RHEL 6
It's my understanding that the following query gives you an idea at the instance level of IOPS requirements for the database.
select METRIC_NAME,avg(AVERAGE) as "Value", max(average) as maxval
from dba_hist_sysmetric_summary
where METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec')
group by METRIC_NAME;
If I wanted to do some planning and determine how many IOPS I should need for a specific user if I pulled out schemas into their own database, what would be the best way to get that information?
I was hoping to use something similar to below, but for long term sessions that have spotty activity the results get skewed.
select ss.sid, ss.username,
to_char(ss.logon_time,'DD-MON-YY HH24:MI:SS') as logintime,
round((SYSDATE - ss.logon_time)*1440*60,2) as SESS_DUR_SECS, sn.name, st.value,
round(st.value/((SYSDATE - ss.logon_time)*1440*60),5) as IOPS
from v$sesstat st, v$session ss, v$statname sn
where st.statistic# = sn.statistic#
and st.sid = ss.sid
and username in ('USER1', 'USER2' )
and (lower(sn.name) like 'physical write total io requests%' or lower(sn.name) like 'physical read total io requests%')
order by 1,5;
should I just add in v$session_event and combine the wait time for IO wait events to divide by?