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.

	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

view raw parse_error.txt hosted with ❤ by GitHub

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.

SQL> ALTER SYSTEM SET "_kks_parse_error_warning"=0 SCOPE=BOTH;

view raw mute_parse_failure.txt hosted with ❤ by GitHub

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';

view raw 10035_event.txt hosted with ❤ by GitHub

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.