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
-
Select the Spotlight on Oracle connection in the Spotlight Browser.
-
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:
- Open Configuration & Memory | Oracle Parameters Page
- 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.