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.
To reduce the temporary IO wait time
Use the SQL & Application Workload | Sort Activity Page to: