Spotlight on Oracle 11.0 - Getting Started Guide

Temporary IO Wait Ratio Alarm

Spotlight raises the Temporary IO Wait Ratio alarm when the Temporary IO Wait Time consumes too much of the DB Time, the total time spent performing user calls on the Oracle database.

About Temporary IO waits

Within Oracle, the term sorting covers a range of activities that includes sorts, aggregations, and hash operations. To perform these activities, Oracle uses allocated work areas within PGA memory.

A sort is most efficient when its allocated work area is large enough to contain all the data AND memory structures needed to perform the sort. If the allocated memory is too small, Oracle stores and retrieves unsorted data in temporary segments on disk. This incurs a temporary IO wait (a direct path write temp wait, followed by one or more direct path read temp waits), which can reduce the efficiency of the sort.

• The least efficient operations are multi-pass operations, where unsorted data is temporarily stored on disk and sorted in a series of I/O operations.
• More efficient, but still undesirable, are one-pass operations, where unsorted data is temporarily stored on disk and sorted in a single I/O operation.

To reduce the temporary IO wait time

Use the SQL & Application Workload | Sort Activity Page to:

• Identify the SQL statements responsible for inefficient sorts.
• Identify and end the user sessions responsible for inefficient sorts.
• Allocate PGA memory to ensure more sorts are processed entirely in memory.