Re: why are statements being reparsed even with bind variables?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/25
Message-ID: <8j5qat$1da$1@nnrp1.deja.com>#1/1

In article <119f1a94.3e83854e_at_usw-ex0104-025.remarq.com>,   gdas <gdas1NOgdSPAM_at_yahoo.com.invalid> wrote:
> I am hoping someone could lend a tip or at least point me in a
> general direction on what I can check since I couldn't seem to
> find
> much on this in oracle's documentation.
>
> I was hoping that implementing use of bind variables would
> reducing the amount of parsing that oracle would have to do.
>
> I'm trying to make a batch loading module run faster.
>
> However, after implementing the change, I did a scan of the
> v$sqlarea performance view and found no savings in regards to
> the number of parses invoked.
>
> (I'm not exactly sure if querying this view is the best way to
> check on the parse calls but it's all I could find in the oracle
> docs.)
>
> Anyway, here's what it said for one statement (similar results
> for other statements):
>
> "insert into frc_run
> (run_id,frc_status,frcc_id,sysad_id,frc_rundate,frc_instby,frc_de
> tailinf,frc_source,frc_source_type)
> values(:1,:2,:3,:4,:5,:6,:7,:8,:9)"
>
> Parse calls: "4,928"
> Executions: "4,928"
>
> Because this statement runs so frequently, I'm assuming that it
> would be in the shared sql area, wouldn't it? Does anyone know
> why it is being reparsed every single time? Or at least point
> me in the right direction for things to investigate?
>

It is in the shared pool -- it found it 4,927 time there.

There are 2 types of parses. A "hard" parse happens when we get a statement for the very first time. We cannot find it in the shared pool so we have to do 100% of the work to get it going. This is very cpu intensive and for a simple select/insert/update -- it can consume 90% or more of the total execution time of that statement.

A "soft" parse happens the second time we get that same statement. We find it in the shared pool -- verify its the same statement and can reuse the work and then do. Now, a very tiny percentage of the total execution time for that statement is in the parse.

If you saw 4,928 discrete statements in your shared pool -- that would be a problem. Seeing 1 statment parsed lots is not the problem (but points out you have solved the problem).

Looking at your statment that you have a "batch module" and the parses = executes, I think you can still do better. You don't mention the language you are using or anything but.... You should parse a statement once and execute it over and over. I can demonstrate with Pro*C:

static void process()
{
varchar stmt[255];

int     x = 10;
int     i;

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    strcpy( stmt.arr, "insert into T values ( :x )" );     stmt.len = strlen( stmt.arr );
    for( i = 0; i < 10; i++ )
    {

        exec sql prepare S1 from :stmt;
        exec sql declare C1 cursor for S1;
        exec sql open c1 using :x;

    }

    strcpy( stmt.arr, "insert into T T values ( :x )" );     stmt.len = strlen( stmt.arr );

    exec sql prepare S2 from :stmt;
    exec sql declare C2 cursor for S2;

    for( i = 0, x = 5; i < 10; i++ )
    {

        exec sql open c1 using :x;
    }
}

The first loop parses and executes the statement inside of the loop. It does 10 parses and 10 execs. The second loop however does the same work as the first but it only parses 1 time and executes it 10 times. They achieve the same effect -- one just does it better.

Take a look at HOW you are managing your cursors internally and see if you cannot cut down the number of times you make us parse.

> We're running 8.1.5 on NT
>
> Thanks for your help
> Gavin
>
> Got questions? Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.keen.com
>
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Sun Jun 25 2000 - 00:00:00 CDT