How to Identify Hard Parse Failures (文件 ID 1353015.1)

Applies to:

Goal

Hard parse time may be impacted when there are a high number of parse errors.

This may be noted in the ADDM report as follows:

In the AWR report Parse Failures are recorded in 2 places:

1. Time Model Statistics

2. Instance Activity Stats

......

Solution

Failed parses are not stored in the data dictionary and therefore cannot be identified through querying the data dictionary.

As of Oracle10g,  event 10035 can be set to report SQLs that fail during PARSE operations.

Syntax:

When the event is set,any statement that fails to parse as a result of an error, will be documented in the alert.log, together with the error number and the process OSPID as displayed below

High CPU and Library Cache Contention

A high number of invalid (syntactically incorrect) SQL can result in high CPU and library cache contention. Ideally, the solution is to fix the application to issue valid SQL.
However, as a temporary workaround,  it is possible to set _cursor_features_enabled in order to ease the effect of the incorrect parsing.
When set the parse error is recorded in a table SQLERROR$ which is checked so that repeated attempts to parse syntactically or semantically invalid statements will not continually incur the full costs associated with hard parsing.

In order to enable this workaround,add 32 to the current value of _cursor_features_enabled.

For Example:

The default value of _cursor_features_enabled = 2.
In order  to enable the fix set _cursor_features_enabled to 2 + 32.

After restarting the database certain (not all) parse errors for non-SYS users will be recorded in sqlerror$.

The following notes include information on the necessary fixes to enable this feature. These fixes are included in 112.0.4 and 12.1.0.1

Failed Parse Time and ORA-4025

If a cursor reaches the max threshold for active locks, it can generate lots of ORA-4025 errors and failed parse time will increment very quickly.

See: