From: surman@wwsun10.us.oracle.com (Scott Urman)
Newsgroups: comp.databases.oracle
Subject: Re: ORACLE7 reparses stored procedures all the time (and creates problems)
Date: 10 Oct 1994 16:27:43 GMT
Organization: Oracle Corporation. Redwood Shores, CA
Lines: 61
Distribution: local
Message-ID: <37bq1v$1kr@dcsun4.us.oracle.com>
References: <CxG67F.Mvq@nrigw11.nri.co.jp>
NNTP-Posting-Host: wwsun10.us.oracle.com


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.

I think that this package will solve your problems.  It is owned by SYS, by the
way.

In article <CxG67F.Mvq@nrigw11.nri.co.jp>, y-tamura@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@nri.co.jp