个人总结:

sql 语句的硬分析错误在ADDM报告中可以体现,但是在数据字典中却没有记录,as of oracle 10i (不知道这里为什么提到的是10i) 可以通过10035事件将分析时产生的错误report记录到alert日志里.

疑问:如果同时执行的sql语句很多的话,alert增长量不知道会是什么个情况.

How to Catch Hard Parse Errors [ID 1353015.1]

		Modified 29-AUG-2011 Type HOWTO Status PUBLISHED

In this Document
  Goal
  Solution
  References

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.2 and later [Release: 10.2 and later ]
Information in this document applies to any platform.

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:

FINDING 2: 62% impact (2561 seconds)
------------------------------------
Hard parsing SQL statements that encountered parse errors was consuming
significant database time.

RECOMMENDATION 1: Application Analysis, 62% benefit (2561 seconds)
ACTION: Investigate application logic to eliminate parse errors.

Solution

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

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

Syntax:

ALTER SYSTEM SET EVENTS '10035 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10035 trace name context forever, level 1';
EVENT="10035 trace name context forever, level 1"

Levels:
level 1+ Print out failed parses of SQL statements to

When set, the failing statement, the error raised, and the OS process id will be written to the alert log:

PARSE ERROR: spid=11268, error=904 for statement:
Mon Aug 29 09:48:24 2011
select empid from emp

PARSE ERROR: spid=1776, error=936 for statement:
Mon Aug 29 09:21:30 2011
select * from emp where empno =

PARSE ERROR: spid=10220, error=942 for statement:
Mon Aug 29 09:49:03 2011
select * from emp_new

How to Catch Hard Parse Errors [ID 1353015.1]

个人总结:

Applies to:

Goal

Solution