oracle 12.2 ：WARNING: too many parse errors

王亮

从12.2开始,如果sql解析失败超过一定次数,会在alert日志中记录类似警告信息,便于对其进行排查
2020-10-05T20:30:50.559569+08:00
ARC0 (PID:29542): Archived Log entry 25044 added for T-1.S-12532 ID 0x6564f341 LAD:1
2020-10-05T20:30:50.626457+08:00
TT02 (PID:29552): SRL selected for T-1.S-12533 for LAD:2
2020-10-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


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

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,短期内无法进行修改,又不想让其在alert中提示,可以通过设置该值为0,实现让其不记录在alert日志中
SQL> alter system set "_kks_parse_error_warning"=0;