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