Parse Error Warnings in database alert.log file

 Too many parse errors - how much do you mean?

I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there.

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 statement 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 the 12.2 release, these failing SQL statements are recorded in alert.log as below if they are called excessively.

In the above example, the "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 statement to be written in the alert.log file?

According to the Doc ID 16945190.8, by default, the diagnostic will dump the parse error along with a warning in alert log every 100 parse errors for a given SQL within a 60-minute period. The sampling interval can be configured with the hidden parameter "_kks_parse_error_warning". 

To mute these warnings.

Also, anytime 10035 KSD (Kernel Service Debugging) event can be used to detect these parse failures.

Diagnostic information for 10035 condition is enriched largely with "Bug 25754281 - Diagnostic Enhancement For Event 10035 To Dump More Details About Parse Failures To Alert Log (Doc ID 25754281."

Hope it helps.