7 Replies Latest reply: Jul 11, 2013 2:22 PM by NaranH RSS

    SQL cache case and space insensitivity

    NaranH

      Hi

       

      I think Oracle version 9i onwards supports case insensitive SQL cache, i.e. SQL statements in cache may be matched in a case and space insensitive manner. Please can someone provide an authoritative answer which either confirms my assertion or points me in the right direction where I can check these facts.

       

      I look forward to any responses.

       

      Warm regards,

        • 1. Re: SQL cache case and space insensitivity
          Tubby

          NaranH wrote:

           

          Hi

           

          I think Oracle version 9i onwards supports case insensitive SQL cache, i.e. SQL statements in cache may be matched in a case and space insensitive manner. Please can someone provide an authoritative answer which either confirms my assertion or points me in the right direction where I can check these facts.

           

          I look forward to any responses.

           

          Warm regards,

           

          http://docs.oracle.com/cd/E11882_01/server.112/e16638/memory.htm#i40017

           

          "

          The text of the SQL statements or PL/SQL blocks must be identical, character for character, including spaces, case, and comments.

          "


          To my knowledge this has always been the case. Should be simple enough for you to pull up the documentation for other releases as I did for 11.2 and answer this question yourself now that you know where to look.


          Cheers,

          • 2. Re: SQL cache case and space insensitivity
            Jonathan Lewis


            Best answer is probably a simple demo:

             

             

            SQL> delete /*+ find this */ from t1 where owner = 'XXX';
            
            0 rows deleted.
            
            SQL> DELETE /*+ find this */ from t1 where owner = 'XXX';
            
            0 rows deleted.
            
            SQL> delete /*+ find this */ from    t1   where    owner = 'XXX';
            
            0 rows deleted.
            
            SQL> begin
              2  delete /*+ find this */ from t1 where owner = 'XXX';
              3  end;
              4  /
            
            PL/SQL procedure successfully completed.
            
            SQL> select sql_id, sql_text from v$sql where sql_text like '%find this%' and sql_text not like '%v$sql%';
            
            SQL_ID
            -------------
            SQL_TEXT
            ------------------------------------------------------------------
            7tc7jtr6jjd5r
            delete /*+ find this */ from t1 where owner = 'XXX'
            
            a4ud12x5fxmnw
            begin delete /*+ find this */ from t1 where owner = 'XXX'; end;
            
            bn65fyws830un
            delete /*+ find this */ from    t1   where    owner = 'XXX'
            
            9dumuwpg5b3ks
            DELETE /*+ find this */ from t1 where owner = 'XXX'
            
            9s7qm0xwcm7sc
            DELETE /*+ find this */ FROM T1 WHERE OWNER = 'XXX'
            
            
            5 rows selected.
            
            

            Note that changes in case, or insertion of white space, produces a different SQL_ID but when I put the statement into pl/sql as static SQL it gets "normalized" by the PL/SQL engine into upper case with no excess white space - the latter may be the phenomenon you are thinking of.

             

            Regards

            Jonathan Lewis

            Now on twitter @jloracle

            • 3. Re: SQL cache case and space insensitivity
              NaranH

              Thank you Jonathan for that comprehensive response. Much appreciated.

               

              NaranH

              • 4. Re: SQL cache case and space insensitivity
                BrendanP

                Yep, and initially quite annoying when your sql comment is optimised out in pl/sql so you can't identify the sql id from the comment that you added for just that purpose!

                • 5. Re: SQL cache case and space insensitivity
                  Jonathan Lewis

                  BrendanP wrote:

                   

                  Yep, and initially quite annoying when your sql comment is optimised out in pl/sql so you can't identify the sql id from the comment that you added for just that purpose!

                  That's why I tell people to use a "pseudo-hint" rather than a comment.  I think hint elimination can (or used to) happen from some middle-tier drivers as well.

                   

                  Regards

                  Jonathan Lewis

                  • 6. Re: SQL cache case and space insensitivity
                    Solomon Yakobson

                    Perhaps you were referring to execution plans:

                     

                    SQL> create table t1(owner varchar2(10));

                    Table created.

                    SQL> delete /*+ find this */ from t1 where owner = 'XXX';

                    0 rows deleted.

                    SQL> DELETE /*+ find this */ from t1 where owner = 'XXX';

                    0 rows deleted.

                    SQL> delete /*+ find this */ from    t1   where    owner = 'XXX';

                    0 rows deleted.

                    SQL> begin
                      2  delete /*+ find this */ from t1 where owner = 'XXX';
                      3  end;
                      4  /

                    PL/SQL procedure successfully completed.

                    SQL> select sql_id,plan_hash_value, sql_text from v$sqlarea where sql_text like '%find this%' and sql_text not like '%v$sql
                      2  /

                    SQL_ID        PLAN_HASH_VALUE
                    ------------- ---------------
                    SQL_TEXT
                    --------------------------------------------------------------------------------
                    7tc7jtr6jjd5r       775918519
                    delete /*+ find this */ from t1 where owner = 'XXX'

                    a4ud12x5fxmnw               0
                    begin delete /*+ find this */ from t1 where owner = 'XXX'; end;

                    bn65fyws830un       775918519
                    delete /*+ find this */ from    t1   where    owner = 'XXX'

                    9dumuwpg5b3ks       775918519
                    DELETE /*+ find this */ from t1 where owner = 'XXX'

                    9s7qm0xwcm7sc       775918519
                    DELETE /*+ find this */ FROM T1 WHERE OWNER = 'XXX'

                     

                    SY.

                    • 7. Re: SQL cache case and space insensitivity
                      NaranH

                      Solomon,

                       

                      This was not quite what I was thinking of but I found your answer useful anyway. Being a newbie I find all these explanations very useful to increase my awareness of Oracle.

                       

                      Many thanks for taking the time to respond.

                       

                      NH