| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic SQL Vs Static SQL using Pro*C..
A copy of this was sent to "Nabil Courdy" <moab_at_emirates.net.ae> (if that email address didn't require changing) On Sat, 23 May 1998 21:39:01 -0600, you wrote:
>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
>moab_at_emirates.net.ae
>
>michael twaddell TWA <175501_at_dallas.dseg.ti.com> wrote in message
><35656668.2781E494_at_dallas.dseg.ti.com>...
>>Thomas Kyte wrote:
>>>
>>> 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
>>twaddell_at_ti.com
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
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. Received on Sun May 24 1998 - 00:00:00 CDT
 |
 |