Sort Activity Page
The Sort Activity page displays information on SQL statements associated with high sort activity (Oracle 10.2 and later).
Within Oracle, the term sorting covers a range of PGA (Program Global Area) activities that include not only sorts, but also Aggregation Operations and hash operations. To perform these activities, sorting uses allocated work areas within PGA memory.
If an allocated work area is large enough to contain all the data AND memory structures needed to perform sorts, the sorting operation is at its most efficient. If the allocated work area is too small, Oracle temporarily stores unsorted data on disk, reducing the efficiency of the operation.
- 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 open the Sort Activity page
-
Select the Spotlight on Oracle connection in the Spotlight Browser.
-
Click SQL & Application Workload | Sort Activity.
IO Wait Times Chart
For sorting operations, the most important data series in IO Wait Times are DB Time and Temp IO Wait. If the Temp IO Wait series is too high — that is, if Spotlight has raised the Temporary IO Wait Ratio Alarm, or if the Temp IO Wait value is greater than 30% of the DB Time value — you should:
- Check the SQL Page, Sessions Page, or Active Sorts Page to identify if specific SQL statements or user sessions are causing the problem.
- Check the PGA Target Advisory chart to identify if you need to increase the amount of memory allocated to the PGA.
|
Data |
Description |
|---|---|
|
DB Time |
The rate of time (in milliseconds per second) spent performing user calls on the Oracle database. |
|
Temp IO Wait |
The rate at which time is spent waiting for I/O on temporary files. This is the I/O rate relevant to sorting operations; it includes these waits:
|
|
Direct IO Wait |
The rate at which time is spent waiting on I/O read and write operations that bypass the buffer cache. It includes these waits:
|
|
Buffered IO Wait |
The rate at which time is spent waiting on buffered I/O. It includes these waits:
|
Use PGA Target Advisory to identify whether the amount of memory allocated to the PGA is enough to ensure that the response time for sorting operations is kept to an acceptable (low) value.
|
Data |
Description |
|---|---|
|
Target for Estimate |
The Target for Estimate graph estimates how the response time for operations in the PGA varies with the amount of memory allocated to the PGA. Ideally, the response time is minimized if all PGA operations are performed in memory. Performance suffers when the allocated work area is too small, and Oracle needs to store unsorted data temporarily on disk. Note: In (Oracle 10.2 and later) this is a value calculated from the rate of temporary I/O performed at the given memory setting, multiplied by the average time taken for each I/O operation. In (Oracle 11g), Oracle stores the amount of time spent sorting at the given setting, so this is no longer a calculated value. |
|
Target Allocated Memory |
The vertical dotted line labeled Target Allocated Memory indicates the amount of memory that the Spotlight user has set for the PGA_AGGREGATE_TARGET. Note: Oracle (Oracle 11g) can set this value automatically if Automatic Memory Management has been turned ON via the MEMORY_TARGET parameter. |
|
Currently Allocated Memory |
The vertical dotted line labeled Currently Allocated Memory indicates the amount of memory that is NOW allocated to the PGA.
Note: Consider changing memory allocation only AFTER investigating other solutions. Use the Top SQL by Sort Activity (SQL Page) and Top Sessions by Sort Activity (Sessions Page) tables first to see if you can rewrite the specific SQL statements or end the specific sessions that may be responsible for sort-related problems. To increase the amount of PGA memory allocated to the database:
|