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

From: Youichi Tamura <y-tamura_at_nriws11.nri.co.jp>
Date: Fri, 14 Oct 1994 13:32:22 GMT
Message-ID: <Cxo09z.JB3_at_nrigw11.nri.co.jp>


Adding a message to my own article ...

In article <CxG67F.Mvq_at_nrigw11.nri.co.jp> y-tamura_at_nri.co.jp (Youichi Tamura) writes:
>
>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>."
>
>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.
>
># 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.

When I posted this article a week ago, Oracle called it "spec." Now they changed their mind and decided to call it a "bug", and assigned a bug number. I am truly surprised that a bug of this kind, which is so fundamental to ORACLE7's performance, was discovered at our site, long after ORACLE7 had been released.

Again, anyone with a solution, or a hint to solution?

-- 
							Yoichi Tamura
							y-tamura_at_nri.co.jp
Received on Fri Oct 14 1994 - 14:32:22 CET

Original text of this message