Measuring Disk I/O from within Oracle

Filed in archive Performance on April 23, 2008

© PPDIGITAL

All too often I have seen basically 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, et.) 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.

Before we begin we must understand our applications from a purely I/O perspective. For example do we have an OLTP system or an OLAP system. 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.

It is now time to get dirty and take a look at an Oracle database. Here are some links to Scripts on the net that can help you find out the IOPS and MBPS for your Oracle database. If you are uncertain of what they are doing, comment here, or ask your DBA.


http://www.thecheapdba.com/scripts/vsysstat_ioworkload.sql
Calculate the IOPS for small and large I/O, the percentage of reads to writes, and the MBPS throughput over a period of time as seen by the database.

http://www.thecheapdba.com/scripts/wrh_sysstat_ioworkload.sql
Calculate the IOPS for small and large I/O, the percentage of reads to writes, and the MBPS throughput over snapshots.

http://www.thecheapdba.com/scripts/wrh_sysstat_ioworkload_ALL.sql
Get complete history of IOPS & MBPS from workload repository history and graph it for you management.