(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
exec sql insert into foo values ( 5 );
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:
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
>>> So dynamic sql does not decrease performance but not using bind variables
>>> (in general). There are cases when you don't want to use bind variables
>>> warehouses that rely heavily on the CBO come to mind) but for most
>>> bind variables are the way to go...
>>Could you expand on this for me? I thought that we should always use
>>variables. What is it about Data Warehouse and the Cost Based Optimizer
>>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
>>Administrator's and Application Developer's guides, but cannot find a
>>to this. I really appreciate your input to this newsgroup and would be
>>for any information you can provide. Thanks in advance.
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.