WARNING: too many parse errors

从12.2开始,如果sql解析失败超过一定次数,会在alert日志中记录类似警告信息,便于对其进行排查

2020-01-05T20:30:50.559569+08:00
ARC0 (PID:29542): Archived Log entry 25044 added for T-1.S-12532 ID 0x6564f341 LAD:1
2020-01-05T20:30:50.626457+08:00
TT02 (PID:29552): SRL selected for T-1.S-12533 for LAD:2
2020-01-05T20:40:34.688814+08:00
WARNING: too many parse errors, count=1551097 SQL hash=0xec3987d3
PARSE ERROR: ospid=2240, error=923 for statement:
2020-01-05T20:40:34.688981+08:00
SELECT 1
Additional information: hd=0xef18d268 phd=0xee42c9c8 flg=0x28 cisid=106 sid=106 ciuid=106 uid=106 sqlid=4bwp0wrq3m1ym
...Current username=XIFENFEI
...Application: JDBC Thin Client Action:
2020-01-05T21:00:50.528469+08:00
Thread 1 advanced to log sequence 12534 (LGWR switch)
  Current log# 4 seq# 12534 mem# 0: /u01/app/oracle/oradata/XFF/redo04.log
  Current log# 4 seq# 12534 mem# 1: /u01/app/oracle/fast_recovery_area/XFF/onlinelog/redo04.log
2020-01-05T21:00:50.572028+08:00
ARC1 (PID:29546): Archived Log entry 25046 added for T-1.S-12533 ID 0x6564f341 LAD:1
2020-01-05T21:00:50.636035+08:00
TT02 (PID:29552): SRL selected for T-1.S-12534 for LAD:2
2020-01-05T21:05:32.671478+08:00
WARNING: too many parse errors, count=1551197 SQL hash=0xec3987d3
PARSE ERROR: ospid=4172, error=923 for statement:
2020-01-05T21:05:32.671641+08:00
SELECT 1
Additional information: hd=0xef18d268 phd=0xee42c9c8 flg=0x28 cisid=106 sid=106 ciuid=106 uid=106 sqlid=4bwp0wrq3m1ym
...Current username=XIFENFEI
...Application: JDBC Thin Client Action:

通过查询mos,确认相关记录是通过_kks_parse_error_warning参数来控制,默认每100次解析失败写入alert日志一次

[oracle@kage28 trace]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 5 21:23:21 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> col name for a52
SQL> col value for a24
SQL> col description for a50
set linesize 150
select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  from x$ksppi a,x$ksppcv b
 where a.inst_id = USERENV ('Instance')
   and b.inst_id = USERENV ('Instance')
   and a.indx = b.indx
   and upper(a.ksppinm) LIKE upper('%&param%')
SQL> SQL>   2    3    4    5    6    7  order by name
  8  /
Enter value for param: _kks_parse_error_warning
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_kks_parse_error_warning%')

NAME                                                 VALUE                    DESCRIPTION
---------------------------------------------------- ------------------------ ----------------------
_kks_parse_error_warning                             100                      Parse error warning

由于某种原因,解析错误sql,短期内无法进行修改,又不想让其在alert中提示,可以通过设置该值为0,实现让其不记录在alert日志中

alter system set "_kks_parse_error_warning"=0;

在12.2之前版本,解析错误默认不记录到alert日志中,但是也可以通过event 10035实现类似功能,具体参见:failed parse elapsed time过大分析案例