Oracle Disk I/O Oracle Tips by Burleson Consulting

Disk I/O and Oracle

Oracle tuning techniques are continuously being modified to match changes in the hardware technology.  Having current tuning tools is especially important with data intensive applications like Oracle.  The cost and speed of disk devices have had a considerable impact on Oracle tuning activities.

In 1985, a 1.2 gigabyte disk sold for more than $250,000.  Today, users can buy 100 gigabytes disks for $200 and 100 gigabytes of RAM-disk for $100,000.  The following statements show how storage trends change over time:

§       Disk storage improves tenfold every year. 

§       Storage media becomes obsolete every 25 years.

§       RAM-SAN will replace disks by 2006.

In Oracle, physical disk I/O can be measured by querying STATSPACK and the AWR for the physical disk reads information that is captured inside the stats$filestatxs  and dba_hist_filestatxs tables.

For example, the following Oracle10g script detects all files with physical reads over 10,000 during the snapshot period:

The results yield a running total of Oracle physical reads. The snapshots are collected every hour in this example, and many DBAs will increase the default collection frequency of AWR snapshots. Starting from this script, users could easily add a WHERE clause criteria and create a unique time-series exception report.

SQL> @phys_reads

BEGIN_INTERVAL_TIME FILENAME PHYRDS

------------------------- ---------------------------------------- -------

24-FEB-04 11.00.32.000 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF    164,700

                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF    26,082

                          E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF    472,008

                          E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF      21,794

                          E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA     12,123

24-FEB-04 12.00.32.000 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF    164,700

                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF    26,082

This concept is particularly true when we consider Moore’s Law, which essentially states that processor capacity increases steadily while hardware costs fall and while Moore’s law does not apply to RAM chip characteristics., RAM is steadily falling in cost, but the speed has remained the same for more than 30 years, hovering at about 50 nanoseconds.

---

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts:

Need an Oracle Health Check? Do you have bad performance after an upgrade?   Need to certify that your database follows best practices? BC Oracle performance gurus can quickly certify every aspect of your Oracle database and provide a complete verification that your database is fully optimized.