Author:            SCASTLED
Subject:            How to pin packages in the Shared Pool
Modified:           15 Aug 96 05:33:54           



This package (created via the dbmspool.sql script by user sys)
contains procedures for displaying the sizes of objects in the shared
pool and for marking them for keeping or unkeeping in order to reduce
memory fragmentation. The shared pool is the shared memory area where
cursors and PL/SQL objects are stored. 

If a PL/SQL or cursor object is marked for keeping in the SGA then it
is effectively pinned there until the database is restarted.  Objects 
that are not pinned in the shared pool are liable to be 'aged' out as 
the memory is required for further objects.  Where objects are frequently
swapped in and out of memory, the shared pool can become fragmented
to such an extent that the next time a large object is to be loaded
into memory it will not fit. 

The dbms_shared_pool package comprises three procedures as follows:

1.      procedure sizes (size_in_Kbytes in number)

This procedure shows objects in the shared_pool that are larger than
the specified number of Kbytes.  The information is returned via the
dbms_output package and so a 'set serveroutput on size <n>' command
must be issued prior to the procedure execution from SQL*Plus or
SQLDBA.  The information is obtained from the views v$sqlarea and
v$db_object_cache and is returned as the number of bytes against
either the object name (in the case of packages, tables, views etc.)
or a combination of address and hash number in the form 
'<address>,<hash number>' (for cursors and anonymous blocks).

2.  procedure keep (name in varchar2)

This procedure pins the given object in the shared pool to prevent it
being 'aged' out.  The name may either be the name of a package in the
form 'OWNER.PACKAGE' or the memory location of a cursor or anonymous
block in the form of '<address>,<hash number>'.  Memory locations for
a particular piece of sql or anonymous PL/SQL block can be found by
selecting sql_text, address and hash_value from v$sqlarea.  The 
address and hash_value are as returned in the procedure sizes above.

Restrictions:

When pinning an object via address and hash number, the address must
start with a number.  On platforms where the address starts with a
character (for hexadecimal addresses) a special version of the 
dbmspool.sql script can be obtained from the Oracle Response Centre.
This version of the keep procedure has a second parameter, a single
character, that is set to 'P' when pinning a package (i.e. by name) or
to any other character when pinning via an address and hash number. 
If no second parameter is specified, the default is 'P'.  From 7.1 of
the kernel, this new version of dbmspool.sql will be supplied as
standard.  

Prior to 7.1.6 the only way to keep a procedure or function in the shared
pool is to make it part of a package and then pin that.  From 7.1.6 
onwards procedures may be pinned using the same syntax as for a package.

From 7.2.3 onwards it is possible to pin triggers by using 'R' as the second 
argument to the 'keep' call.

It is currently NOT possible to pin table and view objects. 

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. 

3.  procedure unkeep (name in varchar2)

This procedure has the opposite effect of keep, 'releasing' objects so
that they may then be aged out as required.  The name parameter takes
the same format as in the keep procedure.

Restrictions:

As for keep.

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