Chat now with support
Chat with Support

Spotlight on Oracle 10.4 - Release Notes

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

  1. Select the Spotlight on Oracle connection in the Spotlight Browser.

  2. 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:

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 path read temp
  • Direct path write temp

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:

  • Direct path read
  • Direct path write

Buffered IO Wait

The rate at which time is spent waiting on buffered I/O. It includes these waits:

  • DB file scattered read
  • DB file sequential read
  • DB file single write
  • DB file parallel write
  • Data file init write
  • DB file parallel read

PGA Target Advisory Chart

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.

  • If this value is higher than the Target Allocated Memory, the value set for PGA_AGGREGATE_TARGET is unrealistically low, and Oracle has ignored the limit.
  • If the Y-value of the Target For Estimate graph is significantly higher at Currently Allocated Memory than it is at Target Allocated Memory, you may need to increase the amount of PGA memory allocated to the database.

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:

  1. Open the Configuration & Memory| Oracle Parameters Page.
  2. In the Parameters table, find and change the value of the parameter:
    • PGA_AGGREGATE_TARGET - When allocating memory to the PGA, make sure that the total memory allocated to SGA plus PGA is less than the total memory on your system.
    • MEMORY_TARGET (Oracle 11g and later) - Use MEMORY_TARGET to set the total amount of memory allocated to the database. Oracle then manages SGA and PGA memory automatically.

 

Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating