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:
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.
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.
The KEEP and UNKEEP procedures are used to pin and unpin objects in the Oracle shared pool.
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:
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:
Note the following restrictions on calling KEEP:
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.
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:
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:
Note the following restrictions on calling UNKEEP:
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.
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.
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.
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.
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.
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:
Note the following restrictions on calling ABORTED_THRESHOLD_REQUEST:
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.
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.
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:
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.
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.
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.
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.