Spotlight on Oracle 10.7 - Getting Started Guide

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.

Chart

Description

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.

Data	Description
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.

Data	Description
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.

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

Spotlight on Oracle 10.7 - Getting Started Guide

Parse Activity Page