SQL Page
The rows in Top SQL by Sort Activity indicate the SQL statements whose sorting operations have caused the maximum disk I/O. Look for rows that have the largest values for:
- Estimated Optimal Size: The size of the work area that would be required to process the SQL statement entirely in memory.
- Elapsed Time: The time taken to process the SQL statement.
- Active Time: The time spent sorting the result set for the SQL statement.
- Total Multipass Executions and Total Onepass Executions: SQL statements that are likely to be the least efficient.
The contents of the table may suggest SQL statements that can be rewritten.
To open the SQL page
-
Select the Spotlight on Oracle RAC connection in the Spotlight Browser.
-
Click SQL & Application Workload | Sort Activity Page | SQL.
Top SQL by Sort Activity Grid
Note: Double click on a row in the grid to open the SQL & Application Workload | Top SQL Page for the associated SQL statement.
| Column | Description |
|---|---|
|
SQL Elapsed Time (s) |
The elapsed time used to parse and execute the SQL statement, and fetch the result set. |
|
Operation Time (s) |
The time spent executing the worst performing work area in the SQL statement. This includes any operation that uses a work area (sorts, hash joins, and so on). |
|
Operation Time Ratio |
The ratio of Operation Time to the total operation time for all cursors, including ones NOT in the Top SQL by Sort Activity table. |
| Total active time(s) | Active time for ALL sort operations performed during the execution of the SQL statement. |
|
Estimated Optimal Size |
The estimated size of the work area that would be required to process the SQL statement entirely in memory. |
|
Last Memory Used |
The amount of memory used the previous time the SQL statement was executed. |
|
Max Temp Segment |
The maximum size of the temporary segment created on disk while sorting the result set for the SQL statement. No entry in this column indicates that all sorting was performed in memory. |
|
Operation Type |
The type of sorting operation that used the work area. |
|
Last Execution |
How the SQL statement was executed the last time (OPTIMAL, ONEPASS, N PASSES). |
|
SQL Executions |
The number of times that the SQL statement was executed. |
|
Total Optimal Executions |
The number of times that the work area was used for optimal sorting. |
|
Total Onepass Executions |
The number of times that the work area was used for one-pass sorting. |
|
Total Multipass Executions |
The number of times that the work area was used for multi-pass sorting. |
|
Parsing Schema Name |
The name of the schema used to parse the SQL statement. |
|
SQL Text |
The text of the executed SQL statement. |
|
Child Number |
The number of the SQL statement's child process that used the work area. |
|
Force Matching Signature |
When the CURSOR_SHARING parameter is set to FORCE, this internal Oracle value determines if a cursor can be shared. |
|
Policy |
The sizing policy for the work area (AUTO or MANUAL). |
| Sort Operations | The number of sorts performed during one execution of the SQL statement. |
| SQL id | The SQL identifier for the executed SQL statement. |
Note: The metrics in the Top SQL by Sort Activity table are derived from data collected from V$SQL and V$SQL_WORKAREA.