AboutIntroduction
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.