1 2 Previous Next 29 Replies Latest reply: Apr 2, 2009 8:49 AM by 296950 RSS

    Parsing

    585321
      Is there a difference between

      select * from emp;
      SELECT * FROM EMP;
      SelecT * FROM Emp ;

      when it comes to parsing of these statements in the library cache ??????
      Thanks!!

      null
        • 1. Re: Parsing
          555329
          Of course yes :)
          • 2. Re: Parsing
            585321
            I know there is a difference but why is it so ?
            Cant anything be done to avoid reparsing of such similar statements . I know we need to make sure that all the starements are typed using a similar standard and make sure that is followed by one and all .
            But cant anything be done such that the Oracle Server interprets all such similar statements in the same way and avoid reparsing ?
            • 3. Re: Parsing
              EdStevens
              As long as your apps are using bind variables, this really shouldn't be a problem. All queries presented within code (host language, pkgd procs, sql scripts) will allways be presented exactly the same, so no problem there. All queries presented ad hoc - there'd be no expectation that they would match anything in the cache anyway.

              Is this solving a real, measured problem, or is it a theoretical exercise?
              • 4. Re: Parsing
                SomeoneElse
                But cant anything be done such that the Oracle Server interprets all such similar
                statements in the same way and avoid reparsing ?
                "similar" is a funny word.

                Where do you draw the line?

                select * from "EMP";

                and

                select * from "Emp";

                Even though they kinda look the same they in fact may go after different objects and yield different results.

                If you make the optimizer look deeper at sql statements to see if they really are the same, that will also place a greater burden on the optimizer.
                • 5. Re: Parsing
                  585321
                  Doesnt oracle interpret sql statements in terms of relational algebra or calculus I guess !!!! So how does it make a difference during the interpretation of sql statements if they can be ignored of the case sensitive issue . If everything [sql statements] typed is either interpreted in upper or lower case what kind of additional burden will it put on the optimizer ???

                  null
                  • 6. Re: Parsing
                    SomeoneElse
                    In order to determine if the sql is already in the cache, Oracle only does a simple text comparison.
                    • 7. Re: Parsing
                      Aman....
                      Well the text match is one thing that Oracle does to find out that the statement is exactly the same or not. Also the algorithm takes into the account the ASCII values of everything that you write in the statement. This means that if you use SELECT and then select , both will result will result in the different ASCII values. For the hash of the select to be generated, this serves as an input too. So besides that it would fail with the text match, Oracle's hashing functions would also result in a different hash key that would come out from this mix case or non-matching case thing.
                      Even you can see that hash_values are different for both the cases,
                      Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
                      With the Partitioning, Oracle Label Security, OLAP, Data Mining,
                      Oracle Database Vault and Real Application Testing options
                      
                      SQL> select sql_text,hash_value
                        2  from V$sql
                        3  where sql_text like 'select * from emp';
                      
                      SQL_TEXT
                      --------------------------------------------------------------------------------
                      HASH_VALUE
                      ----------
                      select * from emp
                      1745700775
                      
                      
                      SQL> select sql_text,hash_value
                        2  from V$sql
                        3  where sql_text like 'select * from emp' OR sql_text 'SELECT * FROM EMP';
                      where sql_text like 'select * from emp' OR sql_text 'SELECT * FROM EMP'
                                                                          *
                      ERROR at line 3:
                      ORA-00920: invalid relational operator
                      
                      
                      SQL> select sql_text,hash_value
                        2  from V$sql
                        3  where sql_text like 'select * from emp' OR sql_text like 'SELECT * FROM EMP';
                      
                      SQL_TEXT
                      --------------------------------------------------------------------------------
                      HASH_VALUE
                      ----------
                      SELECT * FROM EMP
                       899955544
                      
                      select * from emp
                      1745700775
                      
                      
                      SQL>
                      Aman....

                      Message was edited by: Added output.
                      Aman....
                      • 8. Re: Parsing
                        Tanel Poder
                        The reason why a statement with even a minor difference in text is parsed as a separate cursor is due how Oracle looks up statements from library cache.

                        First, a little background:

                        Library cache can contain thousands, tens thousands or even hundreds of thousands of cursors (yuck!) The latter can happen especially if SGA_TARGET is in use in conjuction with an application which doesn't use bind variables - this can cause shared pool to grow and grow at the expense of other SGA components, usually buffer cache.

                        Anyway, the key issue is - library cache can contain lots of cursors and thanks how shared pool memory allocation works, these cursors' memory structures can lay anywhere in shared pool heaps.

                        So, now when this new session with a "select * from eMp" query comes in, how do you make sure this statement is not already cached in library cache?

                        One option would be to always scan through whole shared pool in search of such cursor text. This would be disastrous to performance.

                        Another option would be keeping some kind of sorted array or index of SQL texts somewhere in shared pool, with pointers to corresponding cursors heaps where execution plans and other required things are kept. This would also require some kind of index maintenance when new cursors are parsed and old are aged out... and also, it would mean quite a lot of string comparison, especially for long SQL statements. You could end up with having to do thousands of string comparisons for finding the matching SQL - if it exists in the cache at all. If it doesn't exist, this effort is wasted.

                        Oracle (and many other vendors) has taken a different approach. Whenever a new parse request comes in, Oracle calculates hash value from the cursor text and uses this hash value for looking up corresponding library cache "hash chain" which is a linked list of all cursors which text hashes to that bucket.

                        For example if the library cache is organized to 131072 hash buckets and a SQL statement's hash value happens to be 123456789 then a MOD(123456789, 131072) function determines that if the cursor is loaded into library cache, it must exist in library cache hash chain number 118037. Thanks to this rule, the Oracle cursor lookup function (kglget/kksfbc) knows that if this cursor didn't exist under given hash chain, then it is not loaded into library cache at all.


                        So, instead of scanning through whole library cache or doing loads of string comparisons, we can find our cursor with a simple hash calculation function, go to the appropriate hash chain and need to traverse through that only.

                        The problem here is, that even if the SQL text is only a little different from other, it's hash value is very likely going to be different as well (that's just how the hashing works), thus Oracle will look through a different chain of cursors and won't see anything outside it. Therefore Oracle concludes this cursor doesn't exist in cache and needs to be loaded via hard parse.

                        Some tools like Forms and even PL/SQL do try to generate SQL in canonical form, by stripping out comments, whitespace and changing all text to upper (or lower) case, in hope for eliminating some of these differences.

                        In practice, when a classic evil application running a tight loop of generated queries with literal values, virtually all of your hash values will be different. This is why Oracle has come up with CURSOR_SHARING parameter, which will detect literal values, strip them out and replace them with bind variables.

                        There's a single reason for that - this way there will be a single SQL text string for a statement, therefore it's hash value will be the same, therefore the cursor always maps to the same library cache hash bucket and can be found for reuse.

                        --
                        Tanel Poder
                        http://blog.tanelpoder.com
                        • 9. Re: Parsing
                          585321
                          Thanks for the knowledgeable responses !!!! Got to know something new ...especially the hashing done behind the sql!!!!!!

                          But these work only if people take care to follow a certain standard while writing these sql statements and use bind variables etc...

                          But my question from the very beginning was .... cant anything be done to avoid additional burden or hard parsing caused due to not following a standard for the sql statements ???? Cant everything be converted into upper or lower case then calculate the hash values for them ? Isn't that a feasible solution ?
                          Thanks TANEL AND AMAN !!

                          null
                          • 10. Re: Parsing
                            SomeoneElse
                            But my question from the very beginning was .... cant anything be done to avoid additional burden or hard parsing
                            Do you have thousands of users typing in SQL statements freeform?

                            Or are they using an application where the SQL is embedded and can't change (except for the proper use of bind variables)?

                            Your application code may have:
                            SeLeCt eNaMe fRoM EmP wHeRe EmP_iD = :l_eMp_Id

                            But if all your users are all executing the same code, then the select statement will not be hard parsed again (unless it ages out of the cache, sorry).
                            • 11. Re: Parsing
                              SomeoneElse
                              Tanel...nice explanation.
                              • 12. Re: Parsing
                                585321
                                This is something I am trying to learn out of my own interest . I am not facing any problem anywhere!!
                                • 13. Re: Parsing
                                  Tanel Poder
                                  Coding standarads and reuse need to enforce this. As DBA you could write a little SQL statement which goes through V$SQL in the dev/UAT database and reports SQL statements with high "version" counts.

                                  But, unless your developers are really creative, there are only few different ways you can write the same SQL statement (and how many exactly similar SQL statements do you have anyway?). The problem of non-canonical SQL is orders of magnitude smaller than the problem of using literal values where shouldn't.

                                  --
                                  Tanel Poder
                                  http://blog.tanelpoder.com
                                  • 14. Re: Parsing
                                    Aman....
                                    Tanel,
                                    Nice explanation. One small question,the hash_value column that we see from different views, are they representing the Hash vlaue that Oracle created for the statement?For exmaple,if you look my first reply, there are two different hash values generated for different case of the statements.Is it the same that you mentioned or that hash value is some where else located?
                                    For example if the library cache is organized to 131072 hash buckets and a SQL statement's hash value happens to be 123456789 then a MOD(123456789, 131072) function determines that if the cursor is loaded into library cache, it must exist in library cache hash chain number 118037. Thanks to this rule, the Oracle cursor lookup function (kglget/kksfbc) knows that if this cursor didn't exist under given hash chain, then it is not loaded into library cache at all.
                                    I knew about the hash buckets concept but not about the functions.Thanks for that :-).
                                    Regards
                                    Aman....
                                    1 2 Previous Next