#

| Chapter 12 Managing Server Resources |

## 12.2 DBMS_SHARED_POOL: Pinning Objects

The DBMS_SHARED_POOL package provides procedures that allow PL/SQL objects and SQL cursors to be pinned (kept) in the Oracle shared pool. Once pinned, objects are not subject to the normal aging-out processes of the shared pool.

DBMS_SHARED_POOL is used primarily by DBAs to help solve memory management and performance issues that can arise when applications make use of large PL/SQL objects or SQL cursors. Two problems can arise when large objects need to be loaded into the shared pool:

- ORA-04031 errors where insufficient memory is available and the user call fails to execute.

- Degraded performance due to the memory management overhead involved in finding and making room to load large objects.

Pinning large objects into the shared pool when the Oracle instance is first started can reduce or eliminate these problems. Some DBAs use DBMS_SHARED_POOL in their database startup scripts to help ensure that shared pool memory is used efficiently.

### 12.2.1 Getting Started with DBMS_SHARED_POOL

The DBMS_SHARED_POOL package is created when the Oracle database is installed. The dbmspool.sql script (found in the built-in packages source code directory, as described in Chapter 1) contains the source code for this package's specification. Unlike many of the other built-in package scripts, this script is not called by catproc.sql. Thus, the DBA must manually build this package. This is accomplished by executing the dbmspool.sql and prvtpool.plb scripts (in order) from SQLDBA or Server Manager when connected as the INTERNAL user.

Access to the DBMS_SHARED_POOL package is not automatically granted to any users, nor is a public synonym referencing the package created. The package is intended for use strictly by the Oracle DBA, usually when connected as the SYS user. Under Oracle8, the EXECUTE_CATALOG_ROLE role is granted EXECUTE privilege on DBMS_SHARED_POOL, so any users with this role can use the package.

Table 12-2 lists the programs available in this package.

Table 12.2: DBMS_SHARED_POOL Programs
Name | Description | Use in SQL?
--- | --- | ---
ABORTED_REQUEST_ THRESHOLD | Sets size threshold for aborting object loads if memory is low | No
KEEP | Pins object into shared pool | No
SIZES | Displays shared pool objects larger than given size | No
UNKEEP | Unpins object from shared pool | No

DBMS_SHARED_POOL does not declare any exceptions or nonprogram elements.

### 12.2.2 Pinning and Unpinning Objects

The KEEP and UNKEEP procedures are used to pin and unpin objects in the Oracle shared pool.

#### 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.

Parameter | Description
--- | ---
name | Name of the object to pin
flag | Notifies the KEEP procedure of the kind of object specified in the name parameter

The flag parameter can take the following values:

flag Value | Object Type
--- | ---
P or p | Package, procedure, or function name
Q or q (v8 only) | Sequence name
R or r | Trigger name
Any other character | Cursor specified by address and hash value

#### 12.2.2.1.1 Exceptions

The KEEP procedure does not raise any package exceptions. It can raise the following Oracle exceptions if the name parameter does not resolve to a real object or an object of the proper type:

ORA-06564

  Object <object name> does not exist.

ORA-06502

  PL/SQL: numeric or value error.

#### 12.2.2.1.2 Restrictions

Note the following restrictions on calling KEEP:

- TABLE and VIEW objects cannot be pinned using KEEP. Pinning sequences are supported in the Oracle8 version only.

- Oracle warns that the KEEP procedure may not be supported in future releases.

- The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

#### 12.2.2.1.3 Example

This example illustrates using SQL*Plus to pin the package SYS.STANDARD into the shared pool:

>     SQL> BEGIN
>       2     SYS.DBMS_SHARED_POOL.KEEP('SYS.STANDARD','P');
>       3  END;
>       4  /

>     PL/SQL procedure successfully completed.

In the example for DBMS_SHARED_POOL.SIZES, we see that the SYS.STANDARD package is 119 kilobytes in size. This is a good candidate for routinely pinning into the shared pool. Other Oracle packages that are probably good to keep in the shared pool are SYS.DBMS_STANDARD, SYS.DIUTIL, and SYS.DBMS_SYS_SQL.

It is best to pin any objects that are relatively large (larger than 10-20 kilobytes), especially if they are used intermittently. Doing so minimizes the likelihood that dynamic object loading will flush items out of the shared pool to make room for the object.

For an example of pinning a cursor into the shared pool, see the Section 12.2.4, "DBMS_SHARED_POOL Examples"" section.

#### 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.

Parameter | Description
--- | ---
name | Name of the object to unpin
flag | Notifies the UNKEEP procedure of the kind of object specified in the name parameter

The flag parameter can take the following values:

flag Value | Object Type
--- | ---
P or p | Package, procedure, or function name
Q or q (v8 only) | Sequence name
R or r | Trigger name
Any other character | Cursor specified by address and hash value

#### 12.2.2.2.1 Exceptions

The UNKEEP procedure does not raise any package exceptions. It can raise the following Oracle exceptions if the name parameter does not resolve to a real object or an object of the proper type:

ORA-06564

  Object <object name> does not exist.

ORA-06502

  PL/SQL: numeric or value error.

#### 12.2.2.2.2 Restrictions

Note the following restrictions on calling UNKEEP:

- Oracle warns that the UNKEEP procedure may not be supported in future releases.

- The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

#### 12.2.2.2.3 Example

This example releases the object pinned by the KEEP procedure (see the example for KEEP earlier):

>     SQL> BEGIN
>       2     SYS.DBMS_SHARED_POOL.UNKEEP('SYS.STANDARD','P');
>       3  END;
>       4  /

>     PL/SQL procedure successfully completed.

Usually an object is pinned into the shared pool for a reason, so it is not likely that you would need to call UNKEEP regularly. However, if large objects that have been pinned into the shared pool are definitely no longer needed, then memory can be made available to the shared pool by executing UNKEEP on these objects.

### 12.2.3 Monitoring and Modifying Shared Pool Behavior

You can monitor the behavior of objects in the shared pool with the SIZES procedure. You can modify that behavior with the ABORTED_REQUEST_THRESHOLD procedure.

#### 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.1.1 Example

The following example demonstrates using the SIZES procedure in a SQL*Plus session to find all objects currently in the Oracle shared pool using more than 70 kilobytes of memory:

>     SQL> set serveroutput on size 100000
>     SQL> execute SYS.DBMS_SHARED_POOL.SIZES(70);

>     SIZE(K)	KEPT                                                    NAME
>     ------	-------------------------------------------------	----------
>     119	SYS.STANDARD                                            (PACKAGE)
>     87	YES   QDBA.Q$INSTAT                                     (PACKAGE BODY)
>     80	YES   QDBA.Q$BGPROC                                     (PACKAGE BODY)
>     77	YES   QDBA.Q$CVAR                                       (PACKAGE)
>     72	begin :r:="LOADX"."RAND1";end;    (0D953BE8,3990841093)	(CURSOR)

>     PL/SQL procedure successfully completed.

Notice that a cursor object's name is composed of the address and hash value for the cursor. These are the values to use in calls to DBMS_SHARED_POOL.KEEP for pinning cursor objects. See the Section 12.2.4" section for an example of pinning a cursor.

The SIZES procedure is normally used from the SQL*Plus, SQLDBA, or Sever Manager utilities. In order to display the results, issue the SET SERVEROUTPUT ON SIZE NNNNNN command prior to calling this program, as shown in the example.

#### 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.3.2.1 Exceptions

The ABORTED_THRESHOLD_REQUEST procedure does not raise any package exceptions. It can raise the following Oracle exceptions if the threshold_size parameter is out of range:

ORA-20000

  threshold_size not in valid range: (5000 - 2147483647).

#### 12.2.3.2.2 Restrictions

Note the following restrictions on calling ABORTED_THRESHOLD_REQUEST:

- The range of valid values for the threshold_size is 5000 to 2147483647, inclusive.

- The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

#### 12.2.3.2.3 Example

The following shows how to keep objects larger than 50,000 bytes in size from flushing the shared pool when there is insufficient memory to load them:

>     SQL> BEGIN
>       2     SYS.DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD(50000);
>       3  END;
>       4  /

>     PL/SQL procedure successfully completed.

ABORTED_REQUEST_THRESHOLD allows the DBA to control the negative impact of dynamically loading large objects into a fragmented or very active Oracle shared pool. Normally, these objects should be pinned into the shared pool using the KEEP procedure.

By setting the aborted request threshold, the DBA can avoid performance degradation for all users in cases of extreme pressure on shared pool resources. However, this may result in some users receiving the ORA-4031 error. In these cases, the DBA should determine the source of the ORA-4031 errors and pin the appropriate objects into the shared pool using KEEP.

### 12.2.4 DBMS_SHARED_POOL Examples

The DBMS_SHARED_POOL package is quite specialized and is intended for use by Oracle DBAs to help manage shared pool memory allocation problems. It would be unusual to see it used in applications, although a package-based application may try to pin itself into the shared pool using the KEEP procedure.

One problem with the SIZES procedure is that it uses DBMS_OUTPUT to display its results. It is used primarily interactively from the SQL*Plus, SQLDBA, or Server Manager utilities. This is unfortunate, because the natural way to use these results programmatically would be as input to the KEEP procedure. Ambitious DBAs might explore using the UTL_FILE package to pass results from SIZES to KEEP.

#### 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.

Column | Datatype | Description
--- | --- | ---
TOTSIZE | NUMBER | Size in shared pool if object kept (via KEEP)
OWNER | VARCHAR2(30) | Schema of the stored PL/SQL object
NAME | VARCHAR2(30) | Name of the stored PL/SQL object

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.

12.1 DBMS_SPACE: Obtaining Space Information |  | 13. Job Scheduling in the Database

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.