Too many parse errors – how much do you mean?
SQL syntax errors are always normal and commonly dealing with them is up to software developers. But sometimes, they might bother database administrators too.
When an sql is syntactically(E.g. syntax error) or semantically(E.g. projection of a nonexistent column) incorrect, its processing fails at parsing stage and it never gets executed.
If that happens too often, it can have a negative impact on overall database performance. With 12.2 release, these failing sqls are recorded in alert.log as below if they are called excessively.
| 2022-02-17T14:25:33.918516+10:30 | |
| WARNING: too many parse errors, count=9700 SQL hash=0xcccb313a | |
| PARSE ERROR: ospid=33356, error=923 for statement: | |
| 2022-02-17T14:25:33.918632+10:30 | |
| select dual | |
| Additional information: hd=0xb336ab08 phd=0xb336af30 flg=0x28 cisid=120 sid=120 ciuid=120 uid=120 | |
| 2022-02-17T14:29:04.673714+10:30 | |
| WARNING: too many parse errors, count=9800 SQL hash=0xcccb313a | |
| PARSE ERROR: ospid=34578, error=923 for statement: | |
| 2022-02-17T14:29:04.673839+10:30 | |
| select dual |
In the above example, “select dual” statement is failing with “ORA-00923: FROM keyword not found where expected” error code. This is a syntax error, which is observed 100 times within 4 minutes and it is recorded.
Now the question is coming. How much amount of failure is required for an sql to got written in the alert.log file?
According to the Doc ID 16945190.8, By default the diagnostic will dump the parse error along with warning in alert log every 100 parse errors for a given SQL within a 60 minutes period. The sampling interval can be configured with the hidden parameter “_kks_parse_error_warning“.
To mute these warnings.
| SQL> ALTER SYSTEM SET "_kks_parse_error_warning"=0 SCOPE=BOTH; |
Also, anytime 10035 KSD(Kernel Service Debugging) event can be used to detect these parse failures.
| Until explicitly disabled, it will track sql statements with parsing errors. | |
| SQL> alter system set events '10035 trace name context forever, level 1'; | |
| Levels: | |
| level 1+ Print out user SQL statements | |
| level >3 Print out recursive SQL | |
| level >5 Print out row cache cached cursor stats | |
| Turning off : | |
| SQL> alter system set events '10035 trace name context off'; |
Diagnostic information for 10035 condition enriched largely with “Bug 25754281 – Diagnostic Enhancement For Event 10035 To Dump More Details About Parse Failures To Alert Log (Doc ID 25754281.8)“
Hope it helps.
Osman’s DBlog 
Leave your comment