Measuring Disk I/O

Introduction

Do you know how your disk subsystem is actually performing?
This article looks at extracting various I/O statistics so that you can monitor
and determine just how well your disks are doing.

How can I separate Oracle I/O to maximize performance?

Should I separate data files from index files?

Should I separate redo logs

These question(s), AND many more, seem to flood our minds as
database administrators. They are easy to answer with generalities but in
practice can be very difficult to come to a conclusion on unless we look at how
our disk subsystem is actually performing.

Many of us might stop reading this article right now, saying
to ourselves that these questions and level of detail is only available to our
system administrators or those that control the disk farm. All too often, I
have seen two different methodologies when configuring Oracle on storage. The
first is to use Oracle’s Flexible Architecture (OFA) approach where architects
will separate Oracle object types (data, index, redo, archive, etc.) across a
storage array. The second approach is to architect a JBOD (Just a Bunch Of
Disks) configuration and throw everything on it. Both of these approaches lack
the planning and configuration that ultimately produces a well-tuned database
system. They are just taking a shot in the dark, hoping everything is going to
work well because they followed a predefined methodology. Well, methodologies
may not work in your case. Don’t look to your system administrators as they might
not even know how to extract information themselves, and when they do it is
usually at a higher level since they too can not relate the information to the
Oracle stack.

So the DBA must begin to understand the application from a
purely I/O perspective, relay that information to the Storage Administrator and
then, together, develop a plan for configuring or altering a storage subsystem
that will be able to service the application mix. For the storage, a key
performance indicator of an OLTP environment is based on I/Os per second (IOPS)
and latencies (I/O turn-around time). OLAP databases are your data warehouses
or reporting systems and are categorized by moving large amounts of data that
is mostly read only. For the storage array, the performance of an OLAP
environment is based on Mega-bytes per second (MBPS). A database workload is
often descriptive of its application mix. Understanding and translating an
application mix into a database workload is critical for optimizing a storage
system. The workload of an OLTP database is categorized by small random I/O
while OLAP is categorized by large sequential or random I/O.

For the database administrator it is now time to get dirty
and look at the internals of your database. Somewhere and somehow, you must
extract some form of statistics that allow you to categorize the type of SQL
and I/O requests at the database level. Oracle for instance has quite a few
internal tables that allow for the interrogation of this information. For
instance we can query the gv$sysstat view for (‘physical read total IO
requests’ – ‘physical read total multi block requests’) to get the number of
small reads in the system. Do this over a period of time, subtract the
beginning value from the ending value and you quickly get IOPS for small reads
over that period. This MUST be done for each statistic available to get a view
of total IOPS and MBPS being requested by your particular database. These are
the pertinent statistics you will need to extract. Basically, large reads and
writes are used to calculate MBPS and small reads and writes are used for IOPS
calculations.


gv$sysstat (name, value)

Total Reads :’physical read total IO requests’
Total Writes:’physical write total IO requests’

Large Reads :’physical read total multi block requests’
Large Writes:’physical write total multi block requests’
Total Bytes Read :’physical read total bytes’
Total Bytes Written :’physical write total bytes’
To calculate small reads:
Small Reads = Total Reads – Large Reads
Small Writes = Total Writes – Large Writes

Coming up with these numbers allows us to make intelligent
decisions in regards to our current database performance and storage
requirements. Granted, the database may be experiencing contention and actual
I/O requests may be lower then optimal so tuning may be in order. Regardless,
it is the DBAs responsibility to take these IOPS and MBPS to the Storage
Administrator, evaluate if the storage solution is being taxed, and if a reconfiguration
is required. These reconfigurations may require moving data files around to use
more disks, adding more disks for higher throughput, or reducing IOPS and MBPS
through the application. Well, we have come full circle to the application
again. I know you know what to do.

Below are two scripts that will get you started. I have
spent quite a bit of time getting them easy to use and giving you the
information quickly to understand how your disk subsystem is performing over
time.


set echo off
set feedback off
set heading off
set linesize 40
set pagesize 55
set verify off
set termout off
column rpt new_value rpt
select instance_name||’_’||to_char(sysdate,’YYYYMMDDHH24MISS’)||’_vsysstat_ioworkload.LST’ rpt from v$instance;
set termout on
prompt
prompt
prompt ^^^^^^^^^^^^^
prompt Report Name : ../LST/&&rpt
prompt ^^^^^^^^^^^^^
spool ../LST/&&rpt
column sr1 new_value sr1
column sw1 new_value sw1
column lr1 new_value lr1
column lw1 new_value lw1
column tbr1 new_value tbr1
column tbw1 new_value tbw1
set termout off
SELECT
sum(decode(name,’physical read total IO requests’,value,0)- decode(name,’physical read total multi block requests’,value,0)) sr1,
sum(decode(name,’physical write total IO requests’,value,0)- decode(name,’physical write total multi block requests’,value,0)) sw1,
sum(decode(name,’physical read total multi block requests’,value,0)) lr1,
sum(decode(name,’physical write total multi block requests’,value,0)) lw1,
sum(decode(name,’physical read total bytes’,value,0)) tbr1,
sum(decode(name,’physical write total bytes’,value,0)) tbw1
FROM v$sysstat;
set termout on
prompt
prompt
prompt ^^^^^^^^^^^^
prompt First Sample
prompt ^^^^^^^^^^^^
prompt Number of Small Reads : &&sr1
prompt Number of Small Writes: &&sw1
prompt Number of Large Reads : &&lr1
prompt Number of Large Writes: &&lw1
prompt Total Bytes Read : &&tbr1
prompt Total Bytes Written : &&tbw1
prompt
prompt
prompt Enter the amount of time (in seconds) you would like this process to sleep for sampling data
prompt ^^^^^^^^^^^^^^^^^^
prompt Sleep Time (secs): &&sleeptime
prompt ^^^^^^^^^^^^^^^^^^
exec DBMS_LOCK.SLEEP (&&sleeptime);
column sr2 new_value sr2
column sw2 new_value sw2
column lr2 new_value lr2
column lw2 new_value lw2
column tbr2 new_value tbr2
column tbw2 new_value tbw2
set termout off
SELECT
sum(decode(name,’physical read total IO requests’,value,0)- decode(name,’physical read total multi block requests’,value,0)) sr2,
sum(decode(name,’physical write total IO requests’,value,0)- decode(name,’physical write total multi block requests’,value,0)) sw2,
sum(decode(name,’physical read total multi block requests’,value,0)) lr2,
sum(decode(name,’physical write total multi block requests’,value,0)) lw2,
sum(decode(name,’physical read total bytes’,value,0)) tbr2,
sum(decode(name,’physical write total bytes’,value,0)) tbw2
FROM v$sysstat;
set termout on
prompt
prompt
prompt ^^^^^^^^^^^^^
prompt Second Sample
prompt ^^^^^^^^^^^^^
prompt Number of Small Reads : &&sr2
prompt Number of Small Writes: &&sw2
prompt Number of Large Reads : &&lr2
prompt Number of Large Writes: &&lw2
prompt Total Bytes Read : &&tbr2
prompt Total Bytes Written : &&tbw2
prompt
prompt
prompt ^^^^^^^^^
prompt Results :
prompt ^^^^^^^^^
column sri new_value sri
column swi new_value swi
column tsi new_value tsi
column srp new_value srp
column swp new_value swp
column lri new_value lri
column lwi new_value lwi
column tli new_value tli
column lrp new_value lrp
column lwp new_value lwp
column tr new_value tr
column tw new_value tw
column tm new_value tm
SELECT
ROUND((&&sr2-&&sr1)/&&sleeptime,3) sri,
ROUND((&&sw2-&&sw1)/&&sleeptime,3) swi,
ROUND(((&&sr2-&&sr1)+(&&sw2-&&sw1))/&&sleeptime,3) tsi,
ROUND((&&sr2-&&sr1)/DECODE(((&&sr2-&&sr1)+(&&sw2-&&sw1)),0,1,((&&sr2-&&sr1)+(&&sw2-&&sw1)))*100,3) srp,
ROUND((&&sw2-&&sw1)/DECODE(((&&sr2-&&sr1)+(&&sw2-&&sw1)),0,1,((&&sr2-&&sr1)+(&&sw2-&&sw1)))*100,3) swp,
ROUND((&&lr2-&&lr1)/&&sleeptime,3) lri,
ROUND((&&lw2-&&lw1)/&&sleeptime,3) lwi,
ROUND(((&&lr2-&&lr1)+(&&lw2-&&lw1))/&&sleeptime,3) tli,
ROUND((&&lr2-&&lr1)/DECODE(((&&lr2-&&lr1)+(&&lw2-&&lw1)),0,1,((&&lr2-&&lr1)+(&&lw2-&&lw1)))*100,3) lrp,
ROUND((&&lw2-&&lw1)/DECODE(((&&lr2-&&lr1)+(&&lw2-&&lw1)),0,1,((&&lr2-&&lr1)+(&&lw2-&&lw1)))*100,3) lwp,
ROUND(((&&tbr2-&&tbr1)/&&sleeptime)/1048576,3) tr,
ROUND(((&&tbw2-&&tbw1)/&&sleeptime)/1048576,3) tw,
ROUND((((&&tbr2-&&tbr1)+(&&tbw2-&&tbw1))/&&sleeptime)/1048576,3) tm
FROM dual;
SELECT
‘Small Read IOPS = ‘||ROUND((&&sr2-&&sr1)/&&sleeptime,3)||’ IOPS’,
‘Small Write IOPS = ‘||ROUND((&&sw2-&&sw1)/&&sleeptime,3)||’ IOPS’,
‘Total Small IOPS = ‘||ROUND(((&&sr2-&&sr1)+(&&sw2-&&sw1))/&&sleeptime,3)||’ IOPS’,
‘Small Read I/O % = ‘||ROUND((&&sr2-&&sr1)/DECODE(((&&sr2-&&sr1)+(&&sw2-&&sw1)),0,1,((&&sr2-&&sr1)+(&&sw2-&&sw1)))*100,3)||’ %’,
‘Small Write I/O % = ‘||ROUND((&&sw2-&&sw1)/DECODE(((&&sr2-&&sr1)+(&&sw2-&&sw1)),0,1,((&&sr2-&&sr1)+(&&sw2-&&sw1)))*100,3)||’ %’,
‘Large Read IOPS = ‘||ROUND((&&lr2-&&lr1)/&&sleeptime,3)||’ IOPS’,
‘Large Write IOPS = ‘||ROUND((&&lw2-&&lw1)/&&sleeptime,3)||’ IOPS’,
‘Total Large IOPS = ‘||ROUND(((&&lr2-&&lr1)+(&&lw2-&&lw1))/&&sleeptime,3)||’ IOPS’,
‘Large Read I/O % = ‘||ROUND((&&lr2-&&lr1)/DECODE(((&&lr2-&&lr1)+(&&lw2-&&lw1)),0,1,((&&lr2-&&lr1)+(&&lw2-&&lw1)))*100,3)||’ %’,
‘Large Write I/O % = ‘||ROUND((&&lw2-&&lw1)/DECODE(((&&lr2-&&lr1)+(&&lw2-&&lw1)),0,1,((&&lr2-&&lr1)+(&&lw2-&&lw1)))*100,3)||’ %’,
‘Total Read = ‘||ROUND(((&&tbr2-&&tbr1)/&&sleeptime)/1048576,3)||’ MBPS’,
‘Total Written = ‘||ROUND(((&&tbw2-&&tbw1)/&&sleeptime)/1048576,3)||’ MBPS’,
‘Total MBPS = ‘||ROUND((((&&tbr2-&&tbr1)+(&&tbw2-&&tbw1))/&&sleeptime)/1048576,3)||’ MBPS’
FROM dual
;
prompt Small Read IOPS = &&sri IOPS
prompt Small Write IOPS = &&swi IOPS
prompt Total Small IOPS = &&tsi IOPS
prompt Small Read I/O % = &&srp %
prompt Small Write I/O % = &&swp %
prompt Large Read IOPS = &&lri IOPS
prompt Large Write IOPS = &&lwi IOPS
prompt Total Large IOPS = &&tli IOPS
prompt Large Read I/O % = &&lrp %
prompt Large Write I/O % = &&lwp %
prompt Total Read = &&tr MBPS
prompt Total Written = &&tw MBPS
prompt Total MBPS = &&tm MBPS
spool off
undefine sleeptime

Get a complete history of IOPS
& MBPS from workload repository history and graph it for you management.
This allows you to see total database disk activity. Compare this against what
your disk capacity is. Just remember these numbers are for ALL disks. You can
get average IOPS/MBPS by dividing by your total number of disks used in
servicing database requests. This is great information and once you graph the
results you will really see how your I/O, and application performance, might be
suffering during the day or at least determine where peak periods are.


set echo off
set feedback off
set linesize 300
set pagesize 55
set verify off

set termout off
column rpt new_value rpt
select instance_name||’_wrh_sysstat_ioworkload_’||’.LST’ rpt from v$instance;
set termout on
prompt
prompt
prompt ^^^^^^^^^^^^^
prompt Report Name : ../LST/&&rpt
prompt ^^^^^^^^^^^^^
spool ../LST/&&rpt
column sri head “Small|Read|IOPS”
column swi head “Small|Write|IOPS”
column tsi head “Total|Small|IOPS”
column srp head “Small|Read|I/O%”
column swp head “Small|Write|I/O%”
column lri head “Large|Read|IOPS”
column lwi head “Large|Write|IOPS”
column tli head “Total|Large|IOPS”
column lrp head “Large|Read|I/O%”
column lwp head “Large|Write|I/O%”
column tr head “Total|Read|MBPS”
column tw head “Total|Written|MBPS”
column tm head “Total|MBPS”
column begin_time for a25
column end_time for a25
SELECT end_time,
ROUND(sr/inttime,3) sri,
ROUND(sw/inttime,3) swi,
ROUND((sr+sw)/inttime,3) tsi,
ROUND(sr/DECODE((sr+sw),0,1,(sr+sw))*100,3) srp,
ROUND(sw/DECODE((sr+sw),0,1,(sr+sw))*100,3) swp,
ROUND(lr/inttime,3) lri,
ROUND(lw/inttime,3) lwi,
ROUND((lr+lw)/inttime,3) tli,
ROUND(lr/DECODE((lr+lw),0,1,(lr+lw))*100,3) lrp,
ROUND(lw/DECODE((lr+lw),0,1,(lr+lw))*100,3) lwp,
ROUND((tbr/inttime)/1048576,3) tr,
ROUND((tbw/inttime)/1048576,3) tw,
ROUND(((tbr+tbw)/inttime)/1048576,3) tm
FROM (
SELECT beg.snap_id beg_id, end.snap_id end_id,
beg.begin_interval_time, beg.end_interval_time,
end.begin_interval_time begin_time, end.end_interval_time end_time,
(extract(day from (end.end_interval_time – end.begin_interval_time))*86400)+
(extract(hour from (end.end_interval_time – end.begin_interval_time))*3600)+
(extract(minute from (end.end_interval_time – end.begin_interval_time))*60)+
(extract(second from (end.end_interval_time – end.begin_interval_time))*01) inttime,
decode(end.startup_time,end.begin_interval_time,end.sr,(end.sr-beg.sr)) sr,
decode(end.startup_time,end.begin_interval_time,end.sw,(end.sw-beg.sw)) sw,
decode(end.startup_time,end.begin_interval_time,end.lr,(end.lr-beg.lr)) lr,
decode(end.startup_time,end.begin_interval_time,end.lw,(end.lw-beg.lw)) lw,
decode(end.startup_time,end.begin_interval_time,end.tbr,(end.tbr-beg.tbr)) tbr,
decode(end.startup_time,end.begin_interval_time,end.tbw,(end.tbw-beg.tbw)) tbw
FROM
(SELECT dba_hist_snapshot.snap_id, startup_time, begin_interval_time, end_interval_time,
sum(decode(stat_name,’physical read total IO requests’,value,0)-
decode(stat_name,’physical read total multi block requests’,value,0)) sr,
sum(decode(stat_name,’physical write total IO requests’,value,0)-
decode(stat_name,’physical write total multi block requests’,value,0)) sw,
sum(decode(stat_name,’physical read total multi block requests’,value,0)) lr,
sum(decode(stat_name,’physical write total multi block requests’,value,0)) lw,
sum(decode(stat_name,’physical read total bytes’,value,0)) tbr,
sum(decode(stat_name,’physical write total bytes’,value,0)) tbw
FROM wrh$_sysstat, wrh$_stat_name, dba_hist_snapshot
WHERE wrh$_sysstat.stat_id = wrh$_stat_name.stat_id
AND wrh$_sysstat.snap_id = dba_hist_snapshot.snap_id
group by dba_hist_snapshot.snap_id, startup_time, begin_interval_time, end_interval_time) beg,
(SELECT dba_hist_snapshot.snap_id, startup_time, begin_interval_time, end_interval_time,
sum(decode(stat_name,’physical read total IO requests’,value,0)-
decode(stat_name,’physical read total multi block requests’,value,0)) sr,
sum(decode(stat_name,’physical write total IO requests’,value,0)-
decode(stat_name,’physical write total multi block requests’,value,0)) sw,
sum(decode(stat_name,’physical read total multi block requests’,value,0)) lr,
sum(decode(stat_name,’physical write total multi block requests’,value,0)) lw,
sum(decode(stat_name,’physical read total bytes’,value,0)) tbr,
sum(decode(stat_name,’physical write total bytes’,value,0)) tbw
FROM wrh$_sysstat, wrh$_stat_name, dba_hist_snapshot
WHERE wrh$_sysstat.stat_id = wrh$_stat_name.stat_id
AND wrh$_sysstat.snap_id = dba_hist_snapshot.snap_id
group by dba_hist_snapshot.snap_id, startup_time, begin_interval_time, end_interval_time) end
WHERE beg.snap_id + 1 = end.snap_id
)
order by 1
/
spool off

Understanding an application from a purely I/O perspective
is a key aspect of configuring storage. Oracle has a variety of I/O types that
ultimately need to be mapped, sampled, and related to storage. In Oracle’s
case, there is I/O generated by server processes on behalf of users, multiple
database writers, checkpoint activity, logging facilities that not only write
as updates are being done but also the reading from online logs and writing to
archive logs by the archive process, plus a few more and some internals that
determine size and frequency of the I/Os. Oracle is a very complex system of
processes that without understanding your disk I/O patterns is nearly
impossible to configure properly.

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles