I had blogged previously about calculating iops for a non-RAC database instance. It could be a little tricky to get the IOPS for a RAC database.
In the previous blog entry, i had shown sql which allows you to get the iops during peak hours. The following sql can be used to get the iops for all times.
define start_time='03-FEB-2011 10:00' -- please specify this in 'DD-MON-YYYY HH24:MI' format define end_time ='03-MAR-2011 18:00' -- please specify this in 'DD-MON-YYYY HH24:MI' format define rangesize=500 define name='physical read total IO requests' define inst_no=1 undef v_noofmins col mns new_value v_noofmins select trunc((to_date('&end_time', 'DD-MON-YYYY HH24:MI') - to_date('&start_time', 'DD-MON-YYYY HH24:MI'))*1440) mns from dual / col range head " IOPS Range" form a20 col noofslots head "No Of|Minutes" form 999,999 with v2 as ( select /*+materialize*/ to_date('&start_time', 'DD-MON-YYYY HH24:MI')+(rownum/1440) timewithmin from dual connect by rownum < &v_noofmins ) select /*+use_merge(v1 v2)*/ lpad(trunc(vpersec/&rangesize) * &rangesize, 6) ||' - '|| lpad(((trunc(vpersec/&rangesize) + 1) * &rangesize) - 1, 6) range, count(*) noofslots from ( select /*+ full(st) full(ss) use_hash(st)*/ lag(ROUND(CAST(ss.END_INTERVAL_TIME AS DATE)+(1/1440), 'MI')) over(order by ss.snap_id) dsst, ROUND(CAST(ss.END_INTERVAL_TIME AS DATE), 'MI') dest, (st.value-lag(st.value,1,0) over(order by ss.snap_id)) st, round( (st.value-lag(st.value,1,0) over(order by ss.snap_id) ) / round((CAST(ss.END_INTERVAL_TIME AS DATE) - lag(ROUND(CAST(ss.END_INTERVAL_TIME AS DATE), 'MI')) over(order by ss.snap_id)) * 1440 * 60)) vpersec from sys.WRH$_SYSSTAT st, sys.WRM$_SNAPSHOT ss where st.snap_id=ss.snap_id and ss.INSTANCE_NUMBER = &inst_no and st.INSTANCE_NUMBER = &inst_no and ss.dbid=st.dbid and st.stat_id in ( select stat_id from v$statname where upper(name)=upper(trim('&name')) ) and ss.END_INTERVAL_TIME > to_date('&start_time', 'DD-MON-YYYY HH24:MI') and ss.BEGIN_INTERVAL_TIME < to_date('&end_time', 'DD-MON-YYYY HH24:MI') order by 1 ) v1, v2 where v2.timewithmin between v1.dsst and v1.dest and v2.timewithmin between v1.dsst and v1.dest group by lpad(trunc(vpersec/&rangesize) * &rangesize, 6) ||' - '|| lpad(((trunc(vpersec/&rangesize) + 1) * &rangesize) - 1, 6) order by lpad(trunc(vpersec/&rangesize) * &rangesize, 6) ||' - '|| lpad(((trunc(vpersec/&rangesize) + 1) * &rangesize) - 1, 6) /
The output is something like this
No Of IOPS Range Minutes -------------------- ---------- 0 - 499 59 500 - 999 479 1000 - 1499 1804 1500 - 1999 3595 2000 - 2499 8092 2500 - 2999 11107 3000 - 3499 7074 3500 - 3999 3578 4000 - 4499 2342 4500 - 4999 1260 5000 - 5499 539 5500 - 5999 420 7000 - 7499 59 13 rows selected.
For Instance 2 this is the data.
define inst_no=2
No Of IOPS Range Minutes -------------------- ---------- 0 - 499 179 500 - 999 543 1000 - 1499 1680 1500 - 1999 3419 2000 - 2499 4251 2500 - 2999 4503 3000 - 3499 4617 3500 - 3999 3852 4000 - 4499 1976 4500 - 4999 2126 5000 - 5499 1260 5500 - 5999 840 6000 - 6499 540 6500 - 6999 300 7000 - 7499 661 7500 - 7999 240 8000 - 8499 120 17 rows selected.
The question now is, what is the peak iops for both instances combined? Can we just sum up the peaks of the 2 nodes and say that the peak is 15500 – 16000? This might result in overestimation as both nodes might not have peaked at the same time.
I use the following sql to get the IOPS for all (could be more than 2) nodes.
define start_time='03-FEB-2011 10:00' -- please specify this in 'DD-MON-YYYY HH24:MI' format define end_time ='03-MAR-2011 18:00' -- please specify this in 'DD-MON-YYYY HH24:MI' format define rangesize=500 define name='physical read total IO requests' undef v_noofmins col mns new_value v_noofmins select trunc((to_date('&end_time', 'DD-MON-YYYY HH24:MI') - to_date('&start_time', 'DD-MON-YYYY HH24:MI'))*1440) mns from dual / col range head " IOPS Range" form a20 col noofslots head "No Of|Minutes" form 999,999 with v2 as ( select /*+ materialize */ to_date('&start_time', 'DD-MON-YYYY HH24:MI')+(rownum/1440) timewithmin from dual connect by rownum < &v_noofmins ) select lpad(trunc(v11.vpersec/&rangesize) * &rangesize, 6) ||' - '|| lpad(((trunc(v11.vpersec/&rangesize) + 1) * &rangesize) - 1, 6) range, count(*) noofslots from ( select /*+use_merge(v1 v2)*/ v2.timewithmin, sum(vpersec) vpersec from ( select /*+ full(st) full(ss) use_hash(st)*/ lag(ROUND(CAST(ss.END_INTERVAL_TIME AS DATE)+(1/1440), 'MI')) over(partition by ss.INSTANCE_NUMBER order by ss.snap_id) dsst, ROUND(CAST(ss.END_INTERVAL_TIME AS DATE), 'MI') dest, (st.value-lag(st.value,1,0) over(partition by ss.INSTANCE_NUMBER order by ss.snap_id)) st, round( (st.value-lag(st.value,1,0) over(partition by ss.INSTANCE_NUMBER order by ss.snap_id) ) / round((ROUND(CAST(ss.END_INTERVAL_TIME AS DATE), 'MI') - lag(ROUND(CAST(ss.END_INTERVAL_TIME AS DATE)+(1/1440), 'MI')) over(partition by ss.INSTANCE_NUMBER order by ss.snap_id)) * 1440 * 60)) vpersec from sys.WRH$_SYSSTAT st, sys.WRM$_SNAPSHOT ss where ss.snap_id=st.snap_id and ss.dbid=st.dbid and ss.INSTANCE_NUMBER = st.INSTANCE_NUMBER and st.stat_id in ( select stat_id from v$statname where upper(name)=upper(trim('&name')) ) and ss.END_INTERVAL_TIME > to_date('&start_time', 'DD-MON-YYYY HH24:MI') and ss.BEGIN_INTERVAL_TIME < to_date('&end_time', 'DD-MON-YYYY HH24:MI') order by 1 ) v1, v2 where v2.timewithmin between v1.dsst and v1.dest and v1.st > 0 and v1.dsst is not null group by v2.timewithmin ) v11 group by lpad(trunc(v11.vpersec/&rangesize) * &rangesize, 6) ||' - '|| lpad(((trunc(v11.vpersec/&rangesize) + 1) * &rangesize) - 1, 6) order by lpad(trunc(v11.vpersec/&rangesize) * &rangesize, 6) ||' - '|| lpad(((trunc(v11.vpersec/&rangesize) + 1) * &rangesize) - 1, 6) /
This is an output from our 2-node RAC database.
No Of IOPS Range Minutes -------------------- ---------- 0 - 499 59 1000 - 1499 601 1500 - 1999 359 2000 - 2499 2094 2500 - 2999 5407 3000 - 3499 3303 3500 - 3999 2459 4000 - 4499 1496 4500 - 4999 2224 5000 - 5499 3238 5500 - 5999 3294 6000 - 6499 3482 6500 - 6999 2400 7000 - 7499 2400 7500 - 7999 1558 8000 - 8499 1111 8500 - 8999 1203 9000 - 9499 1019 9500 - 9999 481 10000 - 10499 482 10500 - 10999 300 11000 - 11499 721 11500 - 11999 238 12000 - 12499 241 12500 - 12999 177 13000 - 13499 179 13500 - 13999 61 27 rows selected.
So we can see that the peak was only around 14000.
Just for a little bit of explanation about the above sql.
Get the Number of Minutes between the start_time and end_time with this sql
select trunc((to_date(‘&end_time’, ‘DD-MON-YYYY HH24:MI’) – to_date(‘&start_time’, ‘DD-MON-YYYY HH24:MI’))*1440) mns
from dual
Using sub query factoring, generate a row for each minute between start_time and end_time
with v2 as
(
select /*+ materialize */
to_date(‘&start_time’, ‘DD-MON-YYYY HH24:MI’)+(rownum/1440) timewithmin
from dual
connect by rownum < &v_noofmins
)
In AWR, we have snaps running at regular intervals on all nodes. The start and end timings might not exactly match all the time.
Lets say we take hourly snaps. So from AWR, we get the value of the stat (lets say “physical read total IO requests”) for 1 hour.
(st.value-lag(st.value,1,0) over(partition by ss.INSTANCE_NUMBER order by ss.snap_id))
Then get the per second average
round(
(st.value-lag(st.value,1,0) over(partition by ss.INSTANCE_NUMBER order by ss.snap_id) ) /
round((ROUND(CAST(ss.END_INTERVAL_TIME AS DATE), ‘MI’) –
lag(ROUND(CAST(ss.END_INTERVAL_TIME AS DATE)+(1/1440), ‘MI’))
over(partition by ss.INSTANCE_NUMBER order by ss.snap_id)) * 1440 * 60))
Then join this row with the view V2 so that we get the per second average of data for each instance for each minute.
v2.timewithmin between v1.dsst and v1.dest
Then sum up the data from all instances for each minute.
group by v2.timewithmin
Then generate the IOPS ranges for good presentation of the data
group by lpad(trunc(v11.vpersec/&rangesize) * &rangesize, 6) ||’ – ‘||
lpad(((trunc(v11.vpersec/&rangesize) + 1) * &rangesize) – 1, 6)
I hope i did not mess up this explanation part!
Hi, this is great! However, if we gather snapshots every 30 minutes instead of 1 hour, will the result be different? Which statement should we modify to reflect the frequency of the snapshots?
Thank you very much.
Li
Comment by Li Liu — December 8, 2011 @ 12:42 pm |
Sorry for the delay,
This should work for any snap frequency without any modifications.
regards
srivenu
Comment by srivenu — December 20, 2011 @ 9:49 am |