Oracle數(shù)據(jù)庫的告警日志中出WARNING: too many parse errors這些告警信息的話，如果遇到這個問題，我們應(yīng)該如何分析呢？
下面簡單聊一下如何分析這個錯誤。該告警信息其實是12.2版本中的一個特性增強。在以前的Oracle版本中，數(shù)據(jù)庫出現(xiàn)了解析錯誤時，數(shù)據(jù)庫的alert日志中不會有任何相關(guān)的提示，我們一般只能通過AWR報告才能了解數(shù)據(jù)庫出現(xiàn)了解析錯誤，例如，從"failed parse elapsed time" 和"parse count(failures)"指標中查看解析出錯信息，如下截圖所示：
.....................
如果數(shù)據(jù)庫中解析錯誤的次數(shù)非常頻繁時，可能會造成大量的Library Cache Lock等待，整個數(shù)據(jù)庫可能會處于hang死的狀態(tài)。要找出解析錯誤的root cause，則需要在數(shù)據(jù)庫中設(shè)置10035 event，如果再次出現(xiàn)解析錯誤時，會向數(shù)據(jù)庫的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版本開始，即使未設(shè)置10035 event，當數(shù)據(jù)庫出現(xiàn)解析錯誤的情況時，仍然會向數(shù)據(jù)庫的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:?
這里比較關(guān)鍵的信息是第二行錯誤信息的錯誤代碼："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找到對應(yīng)的SQL，但是有時候，可能通過SQL_ID可能已無法找到SQL語句，只能等到下一次出現(xiàn)時及時定位。
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解析出錯的原因，跟開發(fā)人員一起修復(fù)這個問題，像官方文檔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
出現(xiàn)這個錯誤，是因為應(yīng)用程序中輸入的SQL不完整，沒有from關(guān)鍵字，從錯誤代碼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]。它的現(xiàn)象是Oracle DG的備庫中一直出現(xiàn)"WARNING: too many parse errors",而且數(shù)據(jù)庫版本為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
另外，最重要的就是監(jiān)控數(shù)據(jù)庫的alert日志，一旦出現(xiàn)這類告警就必須發(fā)出告警郵件或告警提示。以前我們監(jiān)控數(shù)據(jù)庫的alert日志，一般是過濾ORA-這類關(guān)鍵字，而這樣過濾的話，是無法獲取WARNING這類的告警信息的。所以監(jiān)控腳本過濾關(guān)鍵字時，必須增加WARNING這個關(guān)鍵字信息。
參考資料
1: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=510335864406137&id=2649163.1&_afrWindowMode=0&_adf.ctrl-state=im4fw4kqq_78文章來源：http://www.zghlxwxcb.cn/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文章來源地址http://www.zghlxwxcb.cn/news/detail-856590.html
到了這里，關(guān)于Oracle數(shù)據(jù)庫出現(xiàn)WARNING: too many parse errors告警的分析思路的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
