PROCEDURE DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 THRESHOLD_SIZE                 NUMBER                  IN

PROCEDURE DBMS_SHARED_POOL.KEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FLAG                           CHAR                    IN     DEFAULT

PROCEDURE DBMS_SHARED_POOL.PURGE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FLAG                           CHAR                    IN     DEFAULT
 HEAPS                          NUMBER                  IN     DEFAULT

PROCEDURE DBMS_SHARED_POOL.SIZES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MINSIZE                        NUMBER                  IN

PROCEDURE DBMS_SHARED_POOL.UNKEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FLAG                           CHAR                    IN     DEFAULT

  

dbms_shared_poolprovides a simple but useful programmatic access to the Oracle SGA's shared pool memory section, specifically where cursors and PL/SQL code are stored. The types of objects supported include:

The PL/SQL programmatic API is as follows:

• Show objects in shared pool > specified size
  procedure sizes(minsize number);

• Keep or pin the object in the shared pool (immune to aging out)
  procedure keep(name varchar2, flag char DEFAULT 'P');

• Unkeep or unpin the object in the shared pool (resume normal aging out)
  procedure unkeep(name varchar2, flag char DEFAULT 'P');

• Forces the object to immediately agre out of the shared pool
  procedure purge(name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1);

• Defines the effective size limit for a pin object request failing
  procedure aborted_request_threshold(threshold_size number);

A very useful example of using the dbms_shared_poolpackage would be to create a database startup trigger to "pin" the most frequently used PL/SQL packages into the shared pool, assuming there is sufficient memory allocation to support it. The pin_top4_procedures.sql PL/SQL code shown next is an example of such a trigger for the specific database where it is known that the four packages chosen see relatively heavy use due to the nature of the database's PL/SQL code:

The following query can be run to help identify which packages might want to be included as pin candidates for the specific database.

SELECT owner, name, type, sharable_mem, loads, kept, executions, locks, pins
FROM v$db_object_cache outer
WHERE type in ('PROCEDURE','PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'TRIGGER', 'SEQUENCE')
  AND kept = 'NO'
  and executions > ( select 2*avg(count(executions))
                     FROM v$db_object_cache inner
                     WHERE type in ('PROCEDURE','PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'TRIGGER', 'SEQUENCE')
                     AND kept = 'NO'
                     group by executions)
  and loads      > ( select 2*avg(count(loads))
                     FROM v$db_object_cache inner
                     WHERE type in ('PROCEDURE','PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'TRIGGER', 'SEQUENCE')
                     AND kept = 'NO'
                     group by loads)
ORDER BY executions DESC;