Re: Excessive parse calls

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 26 Mar 2002 05:17:51 -0800
Message-ID: <a7psdv0mjk@drn.newsguy.com>

In article <4f7d504c.0203252301.226fe380_at_posting.google.com>, vafanassiev_at_aapt.com.au says...
>
>What causes excessive re-parsing of statements with
>bind variables? I have set in init.ora
>

programmers and the applications they write do.

Note that session_cached_cursors is a softer soft parse -- but will still increment the soft parse count.

If a programmer codes something like:

   for i in 1 .. 100 loop

       parse insert statement
       bind insert statement
       execute insert statement
       close insert statement

   end loop

you will see 100 parses (probably 100 soft parses OR 1 hard + 99 soft). If you have session cached cursors set, you will STILL see 100 parses but if you look at v$sesstat you will be able to see that you had 1 real parse offset by 99 session cache hits.

The program SHOULD have coded:

    if ( insert not yet parsed )

         parse insert statement
         insert has been parsed flag = TRUE

    end if
    for i in 1 .. 100 loop

        bind insert
        execute insert

    end loop     

in that fashion, they'll parse that insert ONCE per program execution instead of once per execution...

Note that if a program executes a query ONCE and never again -- by very definition parse = execute. You have to have a program actually execute a statement more then once to see numbers otherwise.

>cursor_space_for_time = true
>open_cursors = 400
>session_cached_cursors = 200
>shared_pool_size = 100,000,000
>
>Stiil, query
>
>select
> sql_text,
> parse_calls,
> executions
>from
> v$sqlarea
>
>shows that many statements are parsed every time they are executed.
>This is very simple application, it does not use outlines,
>optimize_mode = choose, no statistics has been collected for the tables/indexes.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Mar 26 2002 - 07:17:51 CST