错误解析处理

一、现象

alert日志出现如下字样

WARNING: too many parse errors, count=29200 SQL hash=0x2473a808
PARSE ERROR: ospid=7013, error=942 for statement:
2017-05-30T08:19:13.196642-06:00
select col1, col2 from testtab where col1=1;

解释
此处error=942表示 ora-942
addm出现

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.

时间模型出现如下time_model
v$sys_time_model v$sess_time_model

 "failed parse elapsed time" 

awr报告
instance activity stats出现

parsed count(failures)

二、处理方法

错误解析是由应用sql错误导致，数据库侧能做的就是定位错误sql，交由应用侧修改sql

设置追踪

12c版本不用设置追踪，会自动出现在alert中

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"

ALTER SYSTEM SET EVENTS '10035 trace name context off'; --关闭追踪
ALTER SESSION SET EVENTS '10035 trace name context off';

select INSTANCE_NUMBER,TOP_LEVEL_SQL_ID,SQL_ID,count(*) from dba_hist_active_sess_history where IN_HARD_PARSE='Y' group by INSTANCE_NUMBER,TOP_LEVEL_SQL_ID,SQL_ID having count(*)>100 order by count(*) desc;

三、临时解决办法

show parameter _cursor_features_enabled
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_cursor_features_enabled             integer     32

SQL> alter system set "_cursor_features_enabled" = 34 scope=spfile;
System altered.

如果游标达到活动锁的最大阈值，它会产生大量 ORA-4025 错误，并且解析失败的时间会很快增加。