Chatta subito con l'assistenza
Chat con il supporto

Spotlight on Oracle 10.10 - Getting Started Guide

Welcome to Spotlight Install Spotlight Start Spotlight Spotlight on Oracle Spotlight on Oracle Data Guard Spotlight on Oracle RAC Spotlight on Unix Spotlight on Windows Troubleshooting: Connection Problems

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

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

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

 

Related Topics

Related Documents

The document was helpful.

Seleziona valutazione

I easily found the information I needed.

Seleziona valutazione