Oracle Tips by Burleson
Reducing disk I/O in UNIX
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:
* Disk I/O is the largest component of Oracle response time. A
reduction in disk I/O will always result in faster performance for
* 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
So, how do we reduce disk I/O in UNIX? There are three generally
accepted techniques for the DBA to reduce disk I/O:
* 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.
* Change the Oracle SGA - When we increase the shared_pool,
large_pool, or db_cache_size, the resulting performance improvement
is related to the reduction in disk I/O.
* 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.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA