Chat now with support
Chat with Support

Spotlight on Oracle 10.7 - 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 Spotlight on MySQL Troubleshooting: Connection Problems

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

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

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

 

Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating