Re: Dependencies and invalidations.

>My understanding was that SQL contained within the PL/SQL procedures was
>compiled, together with the execution plan. If that is the case, whenever
>SQL plan should change, PL/SQL should be invalidated.

No, not at all. SQL within PL/SQL is parsed when the PL/SQL object first
executes it. A few steps are probably omitted though, namely, referenced
objects validation and privilege checks (they are validated and checked at
compile time, so there's no need to check them again on invocation.) Plans are not stored with PL/SQL - there are outlines for this.

>If, on the other hand, SQL within PL/SQL units is dynamically re-parsed
>whenever the environment changes, the performance benefits of having
>stored procedures over executing anonymous scripts do not exist. Avoiding
>dynamic parsing was one of the biggest promised benefits of using stored
>procedures.

Well, not always. It's dynamically parsed on every invocation for invoker rights PL/SQL only. For definer rights PL/SQL I believe it's parsed once on first execution after the PL/SQL enters the library cache,
because environment is fixed (stats excluded) at compile time and doesn't change at runtime (if it does change, the dependent code is invalidated.) Performance benefits come from various places: we avoid expensive process of PL/SQL compilation (and it became even more expensive in 10g with new optimizing compiler - everything has a price, this new optimization is not free, too); we save on SQL parsing by excluding accessibility/type checks on underlying objects (doesn't apply to invoker rights PL/SQL); we can have PL/SQL compiled natively excluding interpreter overhead at runtime.

Corrections and additions welcome.

Regards,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Mon Apr 24 2006 - 00:24:33 CDT