Donald K. Burleson Oracle Tips

Tips for Reducing disk I/O in Oracle

When using multiple blocksizes, the DBA is given additional control over the UNIX disk I/O sub-system. The Oracle DBA understands the basic truths about UNIX disk I/O:

1. Disk I/O is the largest component of Oracle response time. A reduction in disk I/O will always result in faster performance for that task. 

2. Creating larger data blocks allows Oracle to access more row data in a single I/O. Oracle9i supports multiple block sizes, and the Oracle9i DBA can move tables easily from one blocksize to a tablespace with another blocksize, thereby load-balancing disk I/O.

The Oracle DBA has tools (in-place table reorgs, CTAS with order by) to allow easy table reorganization, and the DBA can use these tools to re-sequence table rows in the same order as the primary index to reduce disk I/O on index range scans.  For more information, see Turning the Tables on Disk I/O, January 2000, at Oracle Magazine online (www.oramag.com).

So, how do we reduce disk I/O in UNIX?  There are three generally accepted techniques for the DBA to reduce disk I/O:

1. Tune SQL statements to retrieve data with a minimum of disk I/O - This is generally performed by finding large-table full-table scans and replacing the full-table scan with an index scan.

2. Change the Oracle SGA  - When we increase the shared_pool_size, large_pool_size, or db_cache_size, the resulting performance improvement is related to the reduction in disk I/O.

3. Reorganize tables to reduce disk I/O – This is done by selectively moving tables to tablespaces with a different blocksize, and re-sequencing table rows into the primary key order.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: