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 |