Parse Activity Page
The Parse Activity page displays information on SQL statements associated with high parse rates (Oracle 10.2 and later).
Parsing is the process of preparing a SQL statement for execution. This involves checking the statement for syntax errors, checking for a matching statement in the shared pool, and determining the optimal execution plan.
- When a SQL statement has been parsed, and the application submitting it leaves the parsing cursor open, the statement may be executed many times without further parsing.
- When the application does NOT leave the cursor open, but can find a match in the shared pool, it performs a soft parse.
- When the application needs to open a new cursor but does not find a match in the shared pool, it performs a hard parse, which can be expensive in terms of CPU and other resources.
Parsing can contribute significantly to processing overhead. When Spotlight raises the Total Parse Wait Time Ratio Alarm , or when you otherwise suspect that parse times may be too high, you can use this Parse Activity page.
To open the Parse Activity page
-
Select the Spotlight on Oracle connection in the Spotlight Browser.
-
Click SQL & Application Workload | Parse Activity.
Charts on the Parse Activity Page
Use the Parse Ratios and Parse Wait Times charts to discover parsing problems.
| Parse Ratios |
Use Parse Ratio to discover the causes of parse activity. For example, hard parsing occurs when a SQL statement being prepared for execution is not found in the shared pool.
| Hard/Parse |
The rate of hard parse calls as a percentage of the total rate of all parse calls. This value is calculated from data collected from V$SYSSTAT. |
| Parse Time |
The time spent parsing SQL statements as a percentage of total active database time (not elapsed time). This value is calculated from data collected from V$SYS_TIME_MODEL. |
| Latch Wait Time |
The time spent waiting for shared-pool latches (including mutexes) as a percentage of total active database time. This value is calculated from data retrieved from V$SYSTEM_EVENT.
High wait times on library cache latches suggest a high level of hard parsing, which may be improved by the use of bind variables. |
| SQL Library Cache Miss |
The number of misses on the SQL AREA library cache as a percentage of gets. This value is calculated from data collected from V$LIBRARYCACHE.
This data series is the same as that shown in the Miss Rates chart of the Activity | Summary Page. | |
| Parse Wait Times |
Use Parse Wait Times to discover which parse activities are consuming the most database time. Wait times are measured in milliseconds per second of elapsed time, and are collected from V$SYS_TIME_MODEL.
|
DB Time |
The amount of elapsed time spent performing user calls on the database. |
|
Latch |
The amount of time spent waiting for latches. |
|
Failed Parse |
The amount of time spent unsuccessfully parsing SQL statements that contain an error (such as a syntax error, for example). |
|
Hard Parse |
The amount of time spent parsing where the SQL statement is not found in the shared pool. |
|
Total Parse |
The amount of time spent parsing SQL statements. |
Note: The data series shown in this chart are not completely independent. For example, Total Parse includes both hard-parse and failed-parse statistics, and some failed parses are also hard parses. |
Related Topics
Spotlight on Oracle Drilldowns
SQL Page | Sessions Page
