硬解析error带来的failed parse elapsed time

客户一套系统在运行跑存储过程的时候比较慢,抓了一下那个时间点的AWR报告,等待事件正常。然后看到有一些failed parse elapsed time。

Statistic Name Time (s) % of DB Time
sql execute elapsed time 72,770.75 95.24
DB CPU 30,850.43 40.38
PL/SQL execution elapsed time 4,015.91 5.26
failed parse elapsed time 1,227.19 1.61

那么failed parse elapsed time是什么含义呢?它代表当我们的sql在进行硬解析的时候,出现了一些错误。主要产生错误的原因可能包含:SQL语法错误,对象不存在,没有足够的权限。我们可以通过10035事件来观察解析sql失败的操作。那么我们试试用10035来跟踪一下失败的sql操作。

[oracle@11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 31 23:38:43 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> connect t1/t1
Connected.

SQL> alter session set events '10035 trace name context forever,level 1';
Session altered.

SQL> select objectid from dba_objects;
select objectid from dba_objects
       *
ERROR at line 1:
ORA-00904: "OBJECTID": invalid identifier

SQL> select * from dba_objects where object_id=;
select * from dba_objects where object_id=
                                         *
ERROR at line 1:
ORA-00936: missing expression

SQL> select * from dba_obj;
select * from dba_obj
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> alter session set events '10035 trace name context off';
Session altered.

然后在alter日志上我们就可以看到trace出来的错误.

Sun Mar 31 23:39:27 2013
PARSE ERROR: ospid=14942, error=904 for statement:
select objectid from dba_objects
Sun Mar 31 23:39:44 2013
PARSE ERROR: ospid=14942, error=936 for statement:
select * from dba_objects where object_id=
PARSE ERROR: ospid=14942, error=942 for statement:
select * from dba_obj
Sun Mar 31 23:43:26 2013
PARSE ERROR: ospid=15000, error=904 for statement:
 select objectid from dba_objects
PARSE ERROR: ospid=15000, error=936 for statement:
select * from dba_objects where object_id=
PARSE ERROR: ospid=15000, error=942 for statement:
select * from dba_obj
后来经过检查,发现在该时间段的确是有大量的错误SQL运行,导致failed parse elapsed time增大,频繁硬解析错误会消耗大量的系统资源。
参考文档:
Resolving Issues Where High ‘failed parse elapsed time’ Seen Due to SQL Receiving Errors on Parse/Execute [ID 1476070.1]
How to Catch Hard Parse Errors [ID 1353015.1]
分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *