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.
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
