Dynamic SQL Vs Static SQL using Pro*C..

Dynamic SQL Vs Static SQL using Pro*C..

Post by michael twaddell TW » Sat, 23 May 1998 04:00:00




> So dynamic sql does not decrease performance but not using bind variables will
> (in general).  There are cases when you don't want to use bind variables (data
> warehouses that rely heavily on the CBO come to mind) but for most applications
> bind variables are the way to go...

Thomas,
Could you expand on this for me?  I thought that we should always use
bind
variables.  What is it about Data Warehouse and the Cost Based Optimizer
that
make this different?  Admittedly, I don't work much with Data Warehouse
applications at the present, but this intrigued me.  I've also looked in
the
Administrator's and  Application Developer's guides, but cannot find a
reference
to this.  I really appreciate your input to this newsgroup and would be
grateful
for any information you can provide.  Thanks in advance.

--
Michael Twaddell
Raytheon Systems

 
 
 

Dynamic SQL Vs Static SQL using Pro*C..

Post by Thomas Ky » Sat, 23 May 1998 04:00:00



(if that email address didn't require changing)



>> So dynamic sql does not decrease performance but not using bind variables will
>> (in general).  There are cases when you don't want to use bind variables (data
>> warehouses that rely heavily on the CBO come to mind) but for most applications
>> bind variables are the way to go...

>Thomas,
>Could you expand on this for me?  I thought that we should always use
>bind
>variables.  What is it about Data Warehouse and the Cost Based Optimizer
>that
>make this different?  Admittedly, I don't work much with Data Warehouse
>applications at the present, but this intrigued me.  I've also looked in
>the
>Administrator's and  Application Developer's guides, but cannot find a
>reference
>to this.  I really appreciate your input to this newsgroup and would be
>grateful
>for any information you can provide.  Thanks in advance.

Ok, lets say you have a table.  Its really big.  Col_A has only 3 values in it,
1, 2, and 3.  90% of the data has a value of 3.  7% of the data has a value of
2.  3% of the data has a value of 1.  You have a bitmapped index on it.  You
issue:

     select * from BigTable where col_A = 1;

That should use the index to access the table (assuming you've analyzed and
given the optimizer the opportunity to discover the massively skewed
distribution of values in col_A).  But later you issue:

     select * from BigTable where col_A = 3;

that should full scan since most of the table will be returned anyway.  That
will happen in the CBO using the above 'hard wired' queries.  Use the CBO with:

     select * from Bigtable where col_a = :x;

and you'll find that it always full scans.  The reason is that it does not know
at COMPILE/PARSE time that :x is discriminating enough (it can't know that, :x
may or may not be discriminating) to get only a small percentage of the table.
Therefore it punts and full scans.  Good for some, bad for others.

Also consider a query like:

    select * from BigTable where col_a like :x;

The optimizer will assume :x could be '%' in the worst case -- a full scan will
happen.  OTOH

    select * from BigTable where col_a like 'ABC%'

will typically (depending on the values in col_a again) index range scan and
then table access by rowid.

So in general for all OLTP type applications -- bind variables are the right
answer.  This is because in general, the parse time for these queries is LARGER
then the actual execute time.  For reporting -- where queries take a long time
to run, not for short lookup queries -- sometimes it makes sense to not use bind
variables when using the CBO to give it more information to go on....

Thomas Kyte

Oracle Government
Herndon VA

http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

 
 
 

Dynamic SQL Vs Static SQL using Pro*C..

Post by Nabil Courd » Sun, 24 May 1998 04:00:00


I have not seen the original post.  But, generally speaking,
static SQL is pro-performance since the optimizer stores and
reuses the optimized query execution  plan.  Thus, resulting in
faster response time for repeated queries.  CBO is the way
to go, forget RBO.

Nabil Courdy




>> So dynamic sql does not decrease performance but not using bind variables
will
>> (in general).  There are cases when you don't want to use bind variables
(data
>> warehouses that rely heavily on the CBO come to mind) but for most
applications
>> bind variables are the way to go...

>Thomas,
>Could you expand on this for me?  I thought that we should always use
>bind
>variables.  What is it about Data Warehouse and the Cost Based Optimizer
>that
>make this different?  Admittedly, I don't work much with Data Warehouse
>applications at the present, but this intrigued me.  I've also looked in
>the
>Administrator's and  Application Developer's guides, but cannot find a
>reference
>to this.  I really appreciate your input to this newsgroup and would be
>grateful
>for any information you can provide.  Thanks in advance.

>--
>Michael Twaddell
>Raytheon Systems


 
 
 

Dynamic SQL Vs Static SQL using Pro*C..

Post by Thomas Ky » Mon, 25 May 1998 04:00:00



(if that email address didn't require changing)


>I have not seen the original post.  But, generally speaking,
>static SQL is pro-performance since the optimizer stores and
>reuses the optimized query execution  plan.  Thus, resulting in
>faster response time for repeated queries.  CBO is the way
>to go, forget RBO.

There is no such thing as 'static sql' ala DB2 in Oracle.  All SQL is dynamic in
Oracle.  We do not store permanently optimized query plans anywhere.

Oracle supports the concept of a shared sql area.  In this area, sql that has
already been parsed and optimized by the database since it was started is
stored.   if anything relating to the parsed/optimized sql changes (eg: an index
which would affect query plans is added to the system or taken away) we flush
the plans and rebuild them later.

No matter which type of sql you use in your application be it "static" or
"dynamic", they both use the shared sql area to equal advantage.  There is no
difference between:

   exec sql insert into foo values ( 5 );

and

   strcpy( string, "insert into foo values ( 5 )" );
   exec sql execute immediate :string;

both will take advantage of shared sql (and since the statements are identicial
in length, case and content -- they will share the same plan actually).  One is
'static' (known at precompile time) and the other dynamic...

Neither statement uses bind variables however which is bad.  that limits reuse.
if we want to insert the value 6, we must parse and build a query plan for a
brand new statement, limiting the effectiveness of the shared sql area (we
aren't sharing plans, we are building a plan per query).  It would be much
better if the above was written as:

...
varchar string[255];
int     x = 6;

...
    exec sql insert into foo values ( :x );

    strcpy( string.arr, "insert into foo values ( :y )" );
    string.len = strlen( string.arr );

    exec sql prepare S from :string;
    exec sql declare C cursor for S;
    exec sql open C using :x;
...

that way, you can simply alter the value of x and repeatedly either "exec sql
insert...." or repeatedly "open C using :x" to simply reuse the cursor and the
associated plans.

>Nabil Courdy




>>> So dynamic sql does not decrease performance but not using bind variables
>will
>>> (in general).  There are cases when you don't want to use bind variables
>(data
>>> warehouses that rely heavily on the CBO come to mind) but for most
>applications
>>> bind variables are the way to go...

>>Thomas,
>>Could you expand on this for me?  I thought that we should always use
>>bind
>>variables.  What is it about Data Warehouse and the Cost Based Optimizer
>>that
>>make this different?  Admittedly, I don't work much with Data Warehouse
>>applications at the present, but this intrigued me.  I've also looked in
>>the
>>Administrator's and  Application Developer's guides, but cannot find a
>>reference
>>to this.  I really appreciate your input to this newsgroup and would be
>>grateful
>>for any information you can provide.  Thanks in advance.

>>--
>>Michael Twaddell
>>Raytheon Systems


Thomas Kyte

Oracle Government
Herndon VA

http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.