This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Apr 2, 2009 6:49 AM by 296950 RSS

Parsing

585321 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    Of course yes :)
  • 2. Re: Parsing
    585321 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    In order to determine if the sql is already in the cache, Oracle only does a simple text comparison.
  • 7. Re: Parsing
    Aman.... Oracle ACE
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Tanel...nice explanation.
  • 12. Re: Parsing
    585321 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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