Oracle数据库的告警日志中出WARNING: too many parse errors这些告警信息的话，如果遇到这个问题，我们应该如何分析呢？
下面简单聊一下如何分析这个错误。该告警信息其实是12.2版本中的一个特性增强。在以前的Oracle版本中，数据库出现了解析错误时，数据库的alert日志中不会有任何相关的提示，我们一般只能通过AWR报告才能了解数据库出现了解析错误，例如，从"failed parse elapsed time" 和"parse count(failures)"指标中查看解析出错信息，如下截图所示：
.....................
如果数据库中解析错误的次数非常频繁时，可能会造成大量的Library Cache Lock等待，整个数据库可能会处于hang死的状态。要找出解析错误的root cause，则需要在数据库中设置10035 event，如果再次出现解析错误时，会向数据库的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"
Levels:
level 1+ Print out failed parses of SQL statements to
Note:
The event can be turned off as follows:
ALTER SYSTEM SET EVENTS '10035 trace name context off';
ALTER SESSION SET EVENTS '10035 trace name context off';
而从12.2版本开始，即使未设置10035 event，当数据库出现解析错误的情况时，仍然会向数据库的alert日志中写入一条解析错误的告警信息。
如下所示，你可能会看到类似这样的报错信息：
2024-04-18T00:26:00.288821+08:00
*******(3):WARNING: too many parse errors, count=592 SQL hash=0xd4b65b68
*******(3):PARSE ERROR: ospid=969851, error=903 for statement:
*******(3):Additional information: hd=0x1c6a4c5b80 phd=0x1f598d0500 flg=0x28 cisid=290 sid=290 ciuid=290 uid=290 sqlid=9mj0cyvabcqv8
*******(3):...Current username=***
*******(3):...Application: IgniteMonitor Action:
这里比较关键的信息是第二行错误信息的错误代码："PARSE ERROR: ospid=969851, error=903 for statement"，这个例子中，它提示SQL解析出错是因为遇到了ORA-903这个错误
$ oerr ora 903
00903, 00000, "invalid table name"
// *Cause: A table or cluster name was invalid or does not exist.
// This message was also issued if an invalid cluster name or no
// cluster name was specified in an ALTER CLUSTER or DROP CLUSTER
// statement.
// *Action: Check spelling. A valid table name or cluster name
// must begin with a letter and may contain only alphanumeric
// characters and the special characters $, _, and #. The name
// must be less than or equal to 30 characters and cannot be a
// reserved word.
我们可以尝试通过SQL_ID找到对应的SQL，但是有时候，可能通过SQL_ID可能已无法找到SQL语句，只能等到下一次出现时及时定位。
SELECT c.username,
,a.program
,b.sql_text
,b.command_type
,a.sample_time
FROM dba_hist_active_sess_history a
JOIN dba_hist_sqltext b
ON a.sql_id = b.sql_id
JOIN dba_users c
ON a.user_id = c.user_id
WHERE a.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
and a.sql_id='9mj0cyvabcqv8'
ORDER BY a.sample_time DESC;
这里如果可以找出具体SQL语句，就可以找出SQL解析出错的原因，跟开发人员一起修复这个问题，像官方文档Doc ID 2649163.1[1]中提及的案例中
2020-01-07T11:35:33.918516+10:30
WARNING: too many parse errors, count=1091700 SQL hash=0xbbcb647d
PARSE ERROR: ospid=33376, error=923 for statement:
2020-01-07T11:35:33.918632+10:30
select 1
Additional information: hd=0xb336ab08 phd=0xb336af30 flg=0x28 cisid=120 sid=120 ciuid=120 uid=120
2020-01-07T11:39:04.673714+10:30
WARNING: too many parse errors, count=1091800 SQL hash=0xbbcb647d
PARSE ERROR: ospid=38578, error=923 for statement:sdkjfdsfkjsadfkjsadfkj
2020-01-07T11:39:04.673839+10:30
select 1
出现这个错误，是因为应用程序中输入的SQL不完整，没有from关键字，从错误代码error=923也能看出出错的可能性。如下所示：
$ oerr ora 923
00923, 00000, "FROM keyword not found where expected"
// *Cause: In a SELECT or REVOKE statement, the keyword FROM was
// either missing, misplaced, or misspelled. The keyword FROM
// must follow the last selected item in a SELECT statement or
// the privileges in a REVOKE statement.
// *Action: Correct the syntax. Insert the keyword FROM where
// appropriate. The SELECT list itself also may be in error. If
// quotation marks were used in an alias, check that double
// quotation marks enclose the alias. Also, check to see if a
// reserved word was used as an alias.
还有一些bug会引起WARNING: too many parse errors，此时就必须在Oracle metalink上进行搜索，仔细匹配了。例如Doc ID 2976229.1 [2]。它的现象是Oracle DG的备库中一直出现"WARNING: too many parse errors",而且数据库版本为Oracle Database - Enterprise Edition - Version 19.3.0.0.0 to 19.21.0.0.0
2023-09-04T13:19:41.797929+00:00
WARNING: too many parse errors, count=13900 SQL hash=0x0cd5bf3b
PARSE ERROR: ospid=15822, error=1219 for statement:
2023-09-04T13:19:41.798049+00:00
select count(*) from cdb_service$
Additional information: hd=0x6cbc40d0 phd=0x6cbc4828 flg=0x20 cisid=0 sid=0
ciuid=2147483620 uid=2147483620 sqlid=<SQL ID>
...Current username=SYSRAC
...Application: oraagent.bin@<HOSTNAME> (TNS V1-V3) Action:
WARNING: too many parse errors, count=13900 SQL hash=0xeb8d02bf
PARSE ERROR: ospid=15822, error=1219 for statement
引起这个的原因是Bug,官方描述如下
The issue is analyzed and discussed in internal / unpublished Bug 34046765 - ORAAGENT DAGENT::SETCONNECTIONPOOLMAX GENERATES TOO MANY PARSE ERRORS ON STANDBY DATABASE
另外，最重要的就是监控数据库的alert日志，一旦出现这类告警就必须发出告警邮件或告警提示。以前我们监控数据库的alert日志，一般是过滤ORA-这类关键字，而这样过滤的话，是无法获取WARNING这类的告警信息的。所以监控脚本过滤关键字时，必须增加WARNING这个关键字信息。
参考资料
1: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=510335864406137&id=2649163.1&_afrWindowMode=0&_adf.ctrl-state=im4fw4kqq_78文章来源：https://www.toymoban.com/news/detail-856590.html
[2]2: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=510414734632502&id=2976229.1&_afrWindowMode=0&_adf.ctrl-state=im4fw4kqq_127文章来源地址https://www.toymoban.com/news/detail-856590.html
到了这里，关于Oracle数据库出现WARNING: too many parse errors告警的分析思路的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
