5 Replies Latest reply on Oct 6, 2017 8:16 AM by Jonathan Lewis

    WARNING: too many parse errors, count= SQL hash  in alert log

    3399716

      Hi All,

       

      i upgraded a production database from 11.2.0.3  to 12.2.0.1  .

       

      After upgrade,  i  see  warnings like below in alert.log.     I searched it in metalink  and found a discussion about it.  İt says      error:923  means  ora-923. and statement  is "SELECT 1" .       Well,  I can not  catch which user is getting this errror.   I m using ospid to find sid but i dont.

      This production database  and Application is same .  Probably,  these case were already present in 11.2.0.3  but it was not writing  these warning to alert.log.   Have you ever met this warnings  in 12.2  ?   Any idea ?  Any parameter  to prevent these warnings in alert.log ?    Thanks advance..

       

      WARNING: too many parse errors, count=7500 SQL hash=0xec3987d3

      PARSE ERROR: ospid=59769040, error=923 for statement:

      2017-10-05T10:00:40.053467+03:00

      SELECT 1

      Additional information: hd=7000100066b2e30 phd=70001000fcad080 flg=0x28 cisid=58 sid=58 ciuid=58 uid=58

        • 1. Re: WARNING: too many parse errors, count= SQL hash  in alert log
          Evgeni Gelfand

          May you can try to find sql_id and full sql textusing sql hash value from the alert.
          As well may be it would be good idea to setup events for 923 error that would help you to receive more information.

           

          Regards

          Evgeni

          1 person found this helpful
          • 2. Re: WARNING: too many parse errors, count= SQL hash  in alert log
            John Thorton

            3399716 wrote:

             

            Hi All,

             

            i upgraded a production database from 11.2.0.3 to 12.2.0.1 .

             

            After upgrade, i see warnings like below in alert.log. I searched it in metalink and found a discussion about it. İt says error:923 means ora-923. and statement is "SELECT 1" . Well, I can not catch which user is getting this errror. I m using ospid to find sid but i dont.

            This production database and Application is same . Probably, these case were already present in 11.2.0.3 but it was not writing these warning to alert.log. Have you ever met this warnings in 12.2 ? Any idea ? Any parameter to prevent these warnings in alert.log ? Thanks advance..

             

            WARNING: too many parse errors, count=7500 SQL hash=0xec3987d3

            PARSE ERROR: ospid=59769040, error=923 for statement:

            2017-10-05T10:00:40.053467+03:00

            SELECT 1

            Additional information: hd=7000100066b2e30 phd=70001000fcad080 flg=0x28 cisid=58 sid=58 ciuid=58 uid=58

            Instead of not writing to alert.log why not actually fix the buggy application so no error ever occurs.

             

            Is the root cause EXECUTE IMMEDIATE?

            • 3. Re: WARNING: too many parse errors, count= SQL hash  in alert log
              Billy~Verreynne

              You have a separate and dedicated app layer? Perhaps using Hybernate?

               

              FIX IT!

               

              That "SELECT 1" cursor parse attempt seems to be an app tier's db abstraction layer confusing the Oracle database as some other database. Never mind the fact that the vast majority of such db abstraction layers got their heads firmly stuck up their backsides, by treating the database layer as a persistent bit bucket storage system.

               

              Your issue should not be with Oracle 12c's complaint in the alert log about being abused. Your focus and actions should be on finding the abuser, and make the abuse stop!

               

              Or is it simply easier to blame the victim of abuse?

              • 4. Re: WARNING: too many parse errors, count= SQL hash  in alert log
                Hemant K Chitale

                You do know what ORA-923 is ? "ORA-00923: FROM keyword not found where expected"

                 

                So your application is sending an incomplete SQL statement to the database engine.  As Billy says, it could have been designed for a different database engine but is being submitted to Oracle.

                 

                OR it could be an incomplete SQL statement itself being generated and submitted.

                 

                 

                Hemant K Chitale

                • 5. Re: WARNING: too many parse errors, count= SQL hash  in alert log
                  Jonathan Lewis

                  It's a feature to reduce the risk of badly behaved connection pooling code from causing contention in the shared pool (see https://jonathanlewis.wordpress.com/2017/10/03/parsing/#comment-101173 ).  It looks like it appeared in 12.2. (See https://jonathanlewis.wordpress.com/2017/10/06/12c-parse/ )

                   

                  I'm inclined to agree with Billy Verreyne's analysis - this looks like your connection pooling software doing a check that the database is still there - but thinks the database is something other than Oracle (which is the only one I know of that requires a "from dual" when you want to do a "select one_row_with_one_constant".

                   

                  Regards

                  Jonathan Lewis