Re: ORACLE7 reparses stored procedures all the time (and creates problems)

y-tamura_at_nri.co.jp writes in article <CxG67F.Mvq_at_nrigw11.nri.co.jp>:
>
>
> I heard something utterly unbelievable from the local Oracle people.
> I hope someone out there could prove them wrong.
>
> As we all know, ORACLE7 supports what's called stored procedures. One
> of the advantages of adopting stored procedures is, as I understood it,
> improved performance. They improve performance because ORACLE7 kernel
> need not reparse a set of PL/SQL procedures once it is called, parsed,
> and "stored" in the shared pool.
>
> That was the sales pitch (which is documented in every manual and textbook).
>
> Now the reality (we use ORACLE7 Server Release 7.0.15.4.2 - Production
> With the procedural and distributed options, on HI-UX): we're in the
> process of developing a large, mission-critical application system for
> our client, using over a hundred stored procedures. We noticed a
> performance problem as we slowly moved into production, examined sql
> trace, and found that stored procedures are loaded into the shared
> pool every time an application calls them (VERSION_COUNT of V$SQLAREA
> simply keeps increasing). So the shared pool soon runs out of space,
> flushing takes place, performance goes down. This happens for users
> granted access to the stored procedures, not for the owner.
>
> Oracle says: "The procedure code (MPCODE) will be copied for each execution,
> as the original procedure is known as <owner name>.<proc name> and we are
> comparing only <proc name>."
>

This behavior is documented (somewhere in the Application Developers Guide, saying that statements can only be reused if they are really identical, including upper/lower case and spaces), but IMHO hard to understand.

However, its the way it works - at least for now.

> They suggested we qualify every procedure with the owner name (and not
> use synonyms for stored procedures) in each and every application program.
> We said that's not an option because that would destroy the independence
> of the application from the ORACLE schema. They said it's the "spec."
> No fix.
>
> Is this true? If it is true, the new feature in ORACLE7 has very little
> to offer. How primitive.

We solved this problem by converting the procedures to packages. With procedures grouped into packages, there is no more parsing - and if you group related procedures into one package, they are all in memory at once.

>
> # Honestly, I don't care if they call it "spec" or "bug" or "problem," so
> # long as they offer a solution. More often than not, they don't.
>
> Again, we use ORACLE7 Server Release 7.0.15.4.2 - Production
> With the procedural and distributed options, on HI-UX.
> ---
> Yoichi Tamura
> y-tamura_at_nri.co.jp
>

Willy Klotz

Willys Mail     FidoNet   2:2474/117  2:2474/118     Mail Only System
                CIS: 100020,3517       USR Courier HST dual standard
                willyk_at_kbigate.franken.de
                willyk_at_kbigate.stgt.sub.org
                ->   No Request from 06.00 to 08.00 <-
======================================================================