Chat now with support
Chat with Support

Spotlight on Oracle 10.10 - Release Notes

SQL Page

Use the information in Top SQL by Hard Parse Time to discover the SQL statements whose execution is causing the highest level of hard parsing on the database.

To open the SQL page

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

  2. Click SQL & Application Workload | Parse Activity Page | SQL.

Top SQL by Hard Parse Time Grid

Notes:

  • The grid may suggest SQL statements that can be rewritten to use bind variables.

    Double click on the grid row to view more information in the SQL statement. Spotlight opens the SQL & Application Workload | Top SQL Page showing all the available details on the execution of the selected statement.

  • The grid may indicate where parse performance can be improved by changing the Oracle CURSOR_SHARING parameter.

    To change the value of the parameter:

    1. Open Configuration & Memory | Oracle Parameters Page
    2. Find CURSOR_SHARING in the Parameters table. Change the value of the parameter. Valid values of the parameter are:
      • EXACT - Only statements with identical text can share a cursor. This is the default value.
      • SIMILAR - Statements that contain different literals (but are otherwise identical) can share a cursor if that sharing does not affect the meaning or execution plan of the statements.
      • FORCE - Statements that contain different literals (but are otherwise identical) can share a cursor if that sharing does not affect the meaning of the statements.
Column Description

Hard Parse Time (s)

Note: Available for Oracle 11g and later.

The total time spent hard-parsing the SQL statement for execution.

Time Wastage (s)

Note: Available for Oracle 11g and later.

The amount of Hard Parse Time wasted by using literals in the SQL statement instead of bind variables.

Hard Parses

The number of times that hard parsing was performed for the SQL statement.

Total Parses

The total number of times that parsing was performed for the SQL statement.

Hard/Total Parse Ratio

The ratio of hard parses to total parses for the SQL statement.

Executions

The number of times the SQL statement has been executed.

Parse/Execution Ratio

The number of times the SQL statement was parsed as a percentage of the number of times the statement was executed.

Total Shared Memory

The total amount of shared-pool memory used by all executions of the SQL statement.

Memory Wastage

The amount of shared-pool memory that was wasted by using literals in the SQL statement instead of bind variables.

Oracle User

The Oracle user that submitted the SQL statement for execution.

Elapsed Time (s)

The total time taken to parse and execute the SQL statement, and to fetch the result set.

Hard Parse/Elapsed Time Ratio

Note: Available for Oracle 11g and later.

The amount of time spent parsing the SQL statement as a percentage of the total time taken to parse and execute the SQL statement, and to fetch the result set.

Explain Plans

The number of explain plans generated for the SQL statement.

Optimizer Mode

The default behavior used when optimizing the execution of the SQL statement. Valid values are ALL_ROWS, FIRST_ROWS, CHOOSE, and RULE.

(In Spotlight, the FIRST_ROWS value includes the Oracle value FIRST_ROWS_n. The rule-based options CHOOSE and RULE are not supported in Oracle 10g and later, but may still be used.)

SQL Text

The text of the SQL statement.

Matching Signature

When the CURSOR_SHARING parameter is set to FORCE, this internal Oracle value determines if a cursor can be shared.

Note: The metrics in the Top SQL by Hard Parse Time table are derived from data collected from V$SQLAREA and V$SQLSTATS.

 

Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating