PARSING STEPS AND BIND VARIABLE : HELP, THOMAS, PLEASE !

From: Philippe LAVIGERIE <plavigerie_at_transiciel.com>
Date: Tue, 10 Dec 2002 15:28:50 +0100
Message-ID: <at4tok$nik$1@s1.read.news.oleane.net>

Thomas,
You always repeat that the parsing steps and the use of bind variables are the most importants things to understand for developpers.

I try to understand exactly the process of parsing SQL Statements based on your website document.
Session_cached_cursors TKYTE
(http://asktom.oracle.com/pls/ask/f?p=4950:8:1699513::NO::F4950_P8_DISPLAYID ,F4950_P8_CRITERIA:2588723819082,%7Bsession_cached_cursors%7D)

Imagine we have two users (U1 & U2) with the same table EMP

The first time the user U1 issue the following statement : SELECT * FROM emp;

the process is the following :
-Soft Parse

1. syntax check
2. semantics check
3. fast hash of the statement and search in the library cache if this hashed

value exist (ADDRESS & HASH VALUE of V$SQLTEXT) meaning 'Does the statement we are currently parsing have already in fact been processed by some other session ?'
-Hard Parse
4. if not, optimization
5. Row source Generation

Now the cursor is cached in SGA.
According to the documentation 'Oracle8i Designing and Tuning for Performance' page 19-20
'Oracle uses the shared SQL area to determine whether more than three parse
requests have been issued on a given statement. If so, Oracle assumes the session
cursor associated with the statement should be cached and moves the cursor into
the session cursor cache. Subsequent requests to parse that SQL statement by the
same session then find the cursor in the session cursor cache.'

So, after 3 parse calls and SESSION_CACHED_CURSORS set to 100 (for example), then it's a SOFTER SOFT PARSE as you call it.

But in the above document (softer soft parse and soft parse, difference December 02, 2002), a reader asked :
'softer soft parse does

1. syntax check
2. semantics check
3. if SQL is in my session cache then execute otherwise look in shared pool'

and you answer :
'softer soft parse does #3, it has to latch in order to tell the database
"hey,
i'm going to use this again" '

OK, but how to squeeze the SOFT PARSE (syntax & semantic check) ? Could you precise, please, how Oracle look into the session cache before the syntax & semantic check or is there always a step like : -Before Syntax and Semantic Check, SQL Statement is hashed and the value is searched only in the SESSION CACHE if it exists and on step 3, the hashed value is searched into Library Cache ?

Now, if the User 2 issue the same statement : SELECT * FROM emp;
You exposed the same case in your document (SQL parsing January 28, 2002) to a reader. And you demonstrate why it was logical to do syntax and semantic check before checking in Library Cache.
For User2, the hashed value of the statement shouldn't be the same as the related objects are not the same as the User1. Does the hash function add the user name before the table name ? If not, how Oracle know that the statement 'SELECT * FROM emp' is not the same for User1 and User2, and the same for the execution plan ?

In 1997, you've written :
'Oracle will first take your SQL query and do a simple, fast hash on it.
this
does not involve parsing. It'll use this hash to try and find the query in the
SGA'.
(http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=336e8f45.5671475%40 newshost&rnum=3)

Does the mechanism of parsing have changed ?

Last question :
You gave an example of C coding to avoid reparse of statement if called multiple times.

    if ( first_time )
    {

        first_time = !first_time;
        sprintf( sqlstmt.arr, "select ename from emp where ename like :b1");
        sqlstmt.len = strlen( sqlstmt.arr );

        printf( "**** Prepared and Declare\n" );
        EXEC SQL PREPARE S FROM :sqlstmt;
        EXEC SQL DECLARE C CURSOR FOR S;

    }

If we omit the test, why the statement should be reparsed as you're using bind variable ?

Thank you, Thomas ! Received on Tue Dec 10 2002 - 08:28:50 CST