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

You can 'pin' a stored procedure in the shared pool. Check out dbmspool.sql, found in $ORACLE_HOME/rdbms/admin. This script creates a package called dbms_pool. One of the procedures in this package is keep(). From this script: .
  procedure keep(name varchar2);

• Keep an object in the shared pool. Once an object has been keeped in
• the shared pool, it is not subject to aging out of the pool. This
• may be useful for certain semi-frequently used large objects since
• when large objects are brought into the shared pool, a larger
• number of other objects (much more than the size of the object
• being brought in, may need to be aged out in order to create a
• contiguous area large enough.

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.
|>
|> 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
Received on Mon Oct 10 1994 - 17:27:43 CET