Chapter 5: Interpreting Common IO Related Wait Events

Overview

I/O operations are an essential part of processing and often represent a major portion of processing time. This is because I/Os are complicated operations involving many layers and components of a computer system, including software components such as Oracle, the disk volume manager, the server operating system, and the disk operating system, as well as hardware components such as buffers, I/O cards, cables, networks, routers, switches, storage controllers, and physical disks. Every component has its own attributes. There are many places that a mismatch or misconfiguration can easily degrade the I/O throughput and application performance.

There are also two basic types of I/O operations: synchronous and asynchronous, which differ in performance. Although hardware engineers have been working for the past decade or so to beef up I/O throughput with various offerings, I/O operations remain the slowest activity in a computer system.

Everyone that touches the computer system needs to be conscientious of the high cost of I/Os. Applications and databases must be designed with efficient data access in mind. Unfortunately, this concept exists mostly in schools and textbooks and seldom in the real business world. Not that there is anything wrong with the concept, but in practice, data access design is often pushed to the back seat because of the unrealistic application delivery dates that are derived from pressure to be the first to the market. As a result, the quality of code suffers and DBAs find numerous SQL statements with poor access paths and joins in the production databases.

This chapter equips you to diagnose and solve problems related to seven common I/O related wait events: db file sequential read, db file scattered read, direct path read, direct path write, log file parallel write, db file parallel write, and controlfile parallel write . You can find their descriptions in Chapter 3.