Subject:            Package Handling in the Shared Pool
Last Revision Date: 27 March     1996
Author:RBEARD



Abstract:               This bulletin addresses problems associated with
                        packages, procedures and functions in the shared pool.
Keywords:               PACKAGE;PIN;SHARED;POOL;DBMS;
________________________________________________________________________________
                       Package Handling in Shared Pool

These recommendations will assist in addressing issues associated with
packages, procedures and functions in the shared pool.

A.      Recommendations

1.      Always pin the following packages in the shared pool:

          STANDARD
          DBMS_STANDARD
          DBMS_UTILITY
          DBMS_DESCRIBE
          DBMS_OUTPUT

and any other SYS packages that are used (DBMS_LOCK, DBMS_ALERT, etc). In
addition, large user written packages and any frequently used packages must
be pinned.

In a development environment, the following packages should also be pinned in
the shared pool:

          PIDL
          DIANA
          DIUTIL

In summary PIN as much as possible, but most importantly, those owned by
SYS and large user written packages.  Currently only packages can be pinned in
the shared pool, therefore it is recommended that procedures and functions are
created as packages.

2.      Package size

With 7.0.13, there was a limit of 64k for the size of a package that could be
successfully compiled and executed. This was removed with 7.0.15.

From 7.0.15, the compiled code for a package was split into more than one
piece, each piece being only about 12k in size. So the 64k restriction was
lifted.  But when a package is executed, and these pieces are loaded into the
shared pool, they must be loaded into a contiguous area of memory - hence you
can get an ora-4031.  With 7.1.5,  it will be possible to reserve an area of 
the shared pool for large pieces, which should effectively solve this
problem. 

Even though, the compiled code for a package is, from 7.0.15, split in smaller
pieces, packages larger than 100k can still have problems compiling. In an
ideal world, when you reach this code limit you would expect to get a compile
error along the lines of 'program too large'. Up until 7.1.5 (bug231459) you
would instead get internal errors, segmentation violations etc.

It is therefore recommended that the code size of a package should not be
greater than 100k.  In addition, any large package must be pinned in the shared
pool to guarantee that there is sufficient memory to execute the package on
subsequent attempts.  It must be remembered that pinning packages takes away
memory available in the shared pool from other resources. Therefore if a large
number of packages are being pinned, the shared pool may need to be increased
in size.

To determine the package code size use the column CODE_SIZE in the view
DBA_OBJECT_SIZE.  The column DBA_OBJECT_SIZE.PARSED_SIZE is the size of the
parsed code for the package.

B. How to pin packages in the shared pool

The procedure dbms_shared_pool.keep should be used to pin a package. The
package dbms_shared_pool is created by running the dbmspool.sql script as
user sys. If you are running 7.1.3 or later, then, in addition,
prvtpool.sql must be run.  Please note that issueing 'keep' does not load 
package immediately into the shared pool. The package must be executed or
recompiled to be loaded. It is recommended that packages are pinned in the
shared pool soon after instance startup, since at this point the sga has
not been fragmented.

When pinning packages, other than those owned by sys, execute privilege on the
package must be granted to public before the call to keep will work.  Once this
has been done the access may then be revoked.

The following sql will look for all packages currently in the sga and generate
a keep script.  It could equally drive off DBA_SOURCE or DBA_OBJECT_SIZE.

SET PAGESIZE 999
SET HEAD OFF
SET FEEDBACK OFF

SPOOL keep_exec.sql

SELECT 
  'execute dbms_shared_pool.keep('''||owner||'.'||name||''');' sql_stmt
  FROM v$db_object_cache
  WHERE type='PACKAGE'
/
SPOOL OFF

An example output is:

execute dbms_shared_pool.keep('SYS.DBMS_STANDARD');
execute dbms_shared_pool.keep('SYS.DBMS_SHARED_POOL');
execute dbms_shared_pool.keep('SYS.DBMS_OUTPUT');
execute dbms_shared_pool.keep('SYS.STANDARD');
execute dbms_shared_pool.keep('SYS.DBMS_UTILITY');

C.      How to unpin packages from the shared pool

The procedure dbms_shared_pool.unkeep should be used to unpin a package.
Issueing the command 'alter system flush shared_pool' does not flush 'keep'ed
packages. They are only aged out of the shared pool by using the procedure
'unkeep'  or restarting the database.

D.      How to find out which packages are pinned in the shared pool:

SELECT name, type, kept
  FROM   v$db_object_cache
  WHERE kept = 'YES';

The column 'KEPT' has the value 'YES' or 'NO'.

or use dbms_shared_pool.sizes to list objects in the shared pool. The second
column 'KEPT' shows whether the package is pinned.

E.      Database Triggers

It is recommended to keep database triggers as small as possible. They should
act as stubs and call packages where necessary.  Packages can, optionally, be
pinned, and do not have to be recompiled when loaded into the shared pool, 
whereas triggers cannot be pinned.

Please note that the keep and unkeep procedures may not be supported in future
releases of the rdbms if automatic mechanisms are implemented to make this
unnecessary.

Known bugs:

196270  ATTEMPT TO RUN A STORED PROCEDURE GIVES ORA 4031
192829  ENH: DO NOT SWAP DB TRIGGERS OUT OF SHARED POOL
187424  NEED UTILITIES TO HELP TUNE SIZING OF SHARED POOL
231459  NOT POSSIBLE TO COMPILE PACKAGES WITH CODE SIZE > 100K