12.2.2.1 The DBMS_SHARED_POOL.KEEP procedure

The KEEP procedure allows DBAs to pin PL/SQL or cursor objects into the Oracle shared pool. Pinning objects into the shared pool eliminates the need for Oracle to do dynamic memory management when users reference the object. The program header follows:

PROCEDURE DBMS_SHARED_POOL.KEEP
   (name IN VARCHAR2
   ,flag IN CHAR DEFAULT 'P');

Parameters are summarized in the following table.

The flag parameter can take the following values:

12.2.2.2 The DBMS_SHARED_POOL.UNKEEP procedure

The UNKEEP procedure allows the DBA to release from the Oracle shared pool a pinned object that has previously been pinned using the KEEP procedure. Once unpinned, the object is subject to the normal shared pool memory management aging and flushing routines. Here's the header for this program:

PROCEDURE DBMS_SHARED_POOL.UNKEEP
   (name IN VARCHAR2
   ,flag IN CHAR DEFAULT 'P');

Parameters are summarized in the following table.

The flag parameter can take the following values:

12.2.3.1 The DBMS_SHARED_POOL.SIZES procedure

The SIZES procedure displays objects (including cursors and anonymous PL/SQL blocks) that are currently in the shared pool and that exceed the size (in kilobytes) specified by the minsize parameter. The program header follows:

PROCEDURE DBMS_SHARED_POOL.SIZES
   (minsize IN NUMBER);

The minsize parameter specifies the minimum size in kilobytes of shared pool objects that are displayed.

The program does not raise any package exceptions, nor does it assert a purity level with the RESTRICT_REFERENCES pragma.

12.2.3.2 The DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD procedure

The ABORTED_REQUEST_THRESHOLD procedure allows the DBA to set a size threshold for restricting Oracle from dynamically flushing unpinned shared pool objects in order to make room for a large object greater than this size. When the threshold is set, any objects larger than the threshold for which sufficient free memory does not exist in the shared pool will fail to load with an ORA-4031 error (rather than flush other objects to make room). The program header follows:

PROCEDURE DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD
    (threshold_size IN NUMBER);

The threshold_size is a NUMBER, in bytes, that specifies the maximum size of objects that can be loaded if shared pool space is not available.

12.2.4.1 Pinning packages automatically

The best time to pin packages into the shared pool is immediately after the Oracle instance is first started and the database mounted. This is when shared pool memory is largely unallocated and has not become fragmented. It is a good DBA practice to call KEEP for any large packages as part of the database startup routine. Under UNIX, the Oracle-supplied script dbstart is often used to start databases. The DBA can customize this script to call KEEP and be sure the objects are pinned.

One thing about database startup and shutdown scripts is that once they are working, you really do not want to modify them unless absolutely necessary. However, the need to pin new packages into the shared pool can come up at any time, and different databases may need to pin different sets of objects. In order to minimize maintenance of database startup scripts, I decided to write a simple procedure called object_keeper, which uses a table of object names and pins all objects in the table when called. Each database's startup script can call object_keeper once to pin all objects, eliminating script maintenance to add or delete objects from the list. The table also allows each database to maintain a separate list of objects to pin.

The table that object_keeper uses is called keep_objects and is created as follows:

//* Filename on companion disk: keeper.sql */*
CREATE TABLE keep_objects
   (
    obj_schema    VARCHAR2(30)  NOT NULL
   ,obj_name      VARCHAR2(30)  NOT NULL
   ,CONSTRAINT ko_PK PRIMARY KEY 
                  (obj_schema, obj_name)
   )
TABLESPACE USER_DATA
STORAGE (INITIAL 2
         NEXT    2
         PCTINCREASE 0);

The object_keeper procedure opens a cursor that joins keep_objects to DBA_OBJECTS and attempts to pin each of the objects in the cursor. Objects in keep_objects not found in DBA_OBJECTS will not be in the cursor, and thus will not attempt to be pinned. The call to DBMS_SHARED_POOL is contained in a BEGIN...END sub-block to allow exception trapping and continuation to the next object in the cursor.

//* Filename on companion disk: keeper.sql */*
CREATE OR REPLACE PROCEDURE object_keeper
   /*
   || Procedure to pin objects into the shared pool
   || using DBMS_SHARED_POOL.KEEP procedure.  All 
   || objects found in the keep_objects table will
   || be KEEPed.
   ||
   || For best results, procedure should be created
   || in the SYS schema.
   ||
   || Author:  John Beresniewicz, Savant Corp
   || Created: 09/18/97
   ||
   || Compilation Requirements:
   ||
   || SELECT on SYS.DBA_OBJECTS
   || EXECUTE on SYS.DBMS_SHARED_POOL
   ||
   || Execution Requirements:
   ||
   || Some SYS objects may get ORA-1031 unless
   || the procedure is run by SYS
   ||
   */
IS
   CURSOR keep_objects_cur
   IS
   SELECT  DO.owner||'.'||DO.object_name  object
          ,DECODE(DO.object_type
                  ,'PACKAGE','P'
                  ,'PROCEDURE','P'
                  ,'FUNCTION','P'
                  ,'TRIGGER','R'
                  ,null
                 )                        type
     FROM  keep_objects  KO
          ,dba_objects   DO
    WHERE UPPER(KO.obj_schema) = DO.owner
      AND UPPER(KO.obj_name)   = DO.object_name
      AND DO.object_type IN 
            ('PACKAGE','PROCEDURE','FUNCTION','TRIGGER');
BEGIN
   FOR ko_rec IN keep_objects_cur
   LOOP
      BEGIN
         SYS.DBMS_SHARED_POOL.KEEP
            (ko_rec.object, ko_rec.type);
         DBMS_OUTPUT.PUT_LINE
            ('KEPT:  '||ko_rec.object);

      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
            DBMS_OUTPUT.PUT_LINE
               ('KEEP FAIL: '||ko_rec.object||' '||ko_rec.type);
      END;
   END LOOP;
END object_keeper;

The object_keeper procedure uses DBMS_OUTPUT to display the results of the calls to KEEP. This is primarily for testing to make sure that all objects actually do get pinned. Use the SET SERVEROUTPUT ON SIZE nnnnnn command to enable the output display.

The following shows the results in SQL*Plus of inserting several rows into keep_objects and executing the object_keeper procedure. This script is available on the disk in the keeptst.sql file.

SQL> INSERT INTO keep_objects
  2  VALUES ('SYS','STANDARD');

1 row created.

SQL> INSERT INTO keep_objects
  2  VALUES ('SYS','DBMS_STANDARD');

1 row created.

SQL> INSERT INTO keep_objects
  2  VALUES ('BOGUS','PACKAGE');

1 row created.

SQL> INSERT INTO keep_objects
  2  VALUES ('SYS','DIUTIL');

1 row created.

SQL> INSERT INTO keep_objects
  2  VALUES ('SYS','DBMS_SQL');

1 row created.

SQL> set serveroutput on size 100000
SQL> execute object_keeper;
KEEPED:  SYS.DBMS_SQL                                                                               
KEEPED:  SYS.DBMS_STANDARD                                                                          
KEEPED:  SYS.DIUTIL                                                                                 
KEEPED:  SYS.STANDARD                                                                               

PL/SQL procedure successfully completed.

12.2.4.2 Pinning cursors into the shared pool

The DBMS_SHARED_POOL.KEEP procedure can be used to pin large cursors into the shared pool, as well as packages, procedures, and functions. In practice, it would be very unusual to need to do this. One reason might be in the case of a very large and complex view definition. Pinning the cursor associated with the view's SELECT statement into the shared pool may avoid memory management issues when users access the view.

In order to pin a cursor, the DBMS_SHARED_POOL.SIZES procedure is used to identify the cursor's address and hash value. These values are then passed as the name parameter in the call to DBMS_SHARED_POOL.KEEP. Note that because the cursor address is not identifiable until after the cursor is already in the shared pool, it is impossible to pre-pin a cursor object prior to its first reference by a user.

The following is output from a SQL*Plus session in which a cursor is identified using the SIZES procedure and is then pinned into the shared pool using KEEP:

SQL> execute dbms_shared_pool.sizes(50);

SIZE(K) 	KEPT   		                                NAME	
------		------------------------------------------	--------------
180		SYS.STANDARD	                                (PACKAGE)                    
78	YES	QDBA.Q$CVAR	                                (PACKAGE)
74	SELECT JOB   FROM SYS.DBA_JOBS_RUNNING  WHERE JOB = :b1 
		(0F884588,518752523)                            (CURSOR)
71	YES	QDBA.Q$INSTAT                                   (PACKAGE BODY)
62	YES	QDBA.Q$BGPROC                                   (PACKAGE BODY)

PL/SQL procedure successfully completed.

SQL> execute dbms_shared_pool.keep('0F884588,518752523','C');

PL/SQL procedure successfully completed.

SQL> execute dbms_shared_pool.sizes(50);

SIZE(K) KEPT   NAME                                                                                 


SIZE(K) 	KEPT   		                                NAME	
------		------------------------------------------	--------------
180		SYS.STANDARD                                    (PACKAGE) 
78	YES	QDBA.Q$CVAR                                     (PACKAGE)	
74	YES(1) 	SELECT JOB   FROM SYS.DBA_JOBS_RUNNING  WHERE JOB = :b1                                   
		(0F884588,518752523)                            (CURSOR)                  
71	YES   	 QDBA.Q$INSTAT                                  (PACKAGE BODY)                    
62	YES    	QDBA.Q$BGPROC                                   (PACKAGE BODY)
PL/SQL procedure successfully completed.

After the cursor is pinned, the second call to the SIZES procedure indicates this by showing "YES" in the KEPT output column. It is interesting (and somewhat confusing) that such a simple SELECT statement results in a cursor that uses 74K of shared pool memory.

12.2.4.3 The DBA_KEEPSIZES view

DBA_KEEPSIZES is a view that makes available the size PL/SQL objects will occupy in the shared pool when kept using the DBMS_SHARED_POOL.KEEP procedure. This view can be used by the DBA to plan for shared pool memory requirements of large PL/SQL objects. The actual view definition, contained in the dbmspool.plb file, follows:

CREATE OR REPLACE VIEW DBA_KEEPSIZES 
   (totsize, owner, name) 
AS
SELECT  TRUNC((SUM(parsed_size)+SUM(code_size))/1000)
       ,owner
       ,name
  FROM dba_object_size
 WHERE TYPE IN
        ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY','TRIGGER')
 GROUP BY owner, name;

The columns for DBA_KEEPSIZES are defined in the following table.

You can query DBA_KEEPSIZES to get an idea of which packages, procedures, and functions are relatively large, and thus may be good candidates for pinning into the shared pool.