Re: Setting bind variables or defines from applications?

Sybrand Bakker wrote:
> On 22 Apr 2006 07:51:32 -0700, "Keith" <keithhoxie_at_verizon.net> wrote:
>
> >Dynamic SQL do not necessarily result in hard parses; depends on the
> >app and how its coded ...
>
>
> Come on! When will you stop posting blatantly incorrect advice here?
>

Sybrand,

what is incorrect about that?

In Oracle, technically speaking, there is only dynamic SQL - all SQL is dynamic under the covers. Oracle does not have "static" SQL in the manner of say DB2 on the mainframe (where the plan is developed once and stored as an access module - across restarts and all)

Dynamic SQL - which is pretty much the only game in town using jdbc, VB, OCI as examples - does not imply

o hard parse
o over parse

It really depends on how the application was coded entirely. A properly coded application will not hard or soft parse anymore than a "static sql" application (written using PLSQL or Pro*C or SQL-J for example) would.

In plsql - if you use "static" sql (meaning PLSQL will dynamically execute it for you at runtime, but the sql never changes from run to run), it is not really possible to bind incorrectly. In PLSQL if you use"dynamic sql" (dbms_sql and or execute immediate), it it possible to not bind correctly which COULD lead to excessive hard parsing - but it is not "a given", it is not automatically going to happen.

for i in 1 .. 1000
loop

   execute immediate 'update t set x = 5 where y = :y' using i; end loop;

That is dynamic sql. In 9iR2 and before you would observe in general:

o at most one hard parse, maybe, if it hadn't been parsed before o 999 to 1000 soft parses - depending on whether we had to hard parse it or not.

In 10g and above, you would observe in general:

o one hard parse OR
o one soft parse

due to a new optimization in PLSQL and how it caches cursors.    

> --
> Sybrand Bakker, Senior Oracle DBA
Received on Mon Apr 24 2006 - 01:09:30 CDT