Oracle Database Administration: The Essential Refe

Disk I/O

Disk access is the slowest operation on any computer system. As a database system, Oracle relies heavily on disk access for storage of and access to data. Consider a typical SQL statement that updates a row of a table. The following operations take place:

1. The data dictionary is read to get information about the table and row being manipulated.

2. The appropriate index is read to locate the row to be updated.

3. The data block containing the row is read.

4. Rollback information is written to a rollback segment.

5. Update information is written to the online log file.

6. The data block is rewritten.

7. The index block is rewritten.

All these operations potentially require disk I/O, although some may be eliminated by efficient use of memory, as we described in the previous section. By making disk I/O as efficient as possible, overall performance will be enhanced. The basic guidelines for maximizing disk I/O are the following:

• Segregate I/O operations to separate disks wherever possible. In this way, there is no need to wait for one disk operation to finish before another is performed. For example, if both the rollback segment and the log file were on the same disk, the rollback record would be written; then the disk head would need to be moved to another part of the disk where the log file record would be written. This would be very time-consuming.

• Place high-I/O-volume disks on different controllers. Most modern controllers can handle a limited number of concurrent operations, but using as many controllers ...