In your C example if you omit the test then you are asking Oracle to
EXEC SQL PREPARE S FROM :sqlstmt;
EXEC SQL DECLARE C CURSOR FOR S;
which generates a parse.
Instead, don't close the cursor (which is why he checks to see if it has
been used), rebind the bind variables and re execute. It is a great
optimization and reduces the load on the server which means you get your
data faster or more people can get data at the same time. (all other things
being equal)
Jim
"Philippe LAVIGERIE" <plavigerie_at_transiciel.com> wrote in message
news:at4tok$nik$1_at_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 - 09:25:21 CST
