132 DBMS_SHARED_POOL

Using DBMS_SHARED_POOL

• Overview

• Operational Notes

---

Overview

The procedures provided here may be useful when loading large PL/SQL objects. When large PL/SQL objects are loaded, users response time is affected because of the large number of smaller objects that need to be aged out from the shared pool to make room (due to memory fragmentation). In some cases, there may be insufficient memory to load the large objects.

DBMS_SHARED_POOL is also useful for frequently executed triggers. You may want to keep compiled triggers on frequently used tables in the shared pool.

Additionally, DBMS_SHARED_POOL supports sequences. Sequence numbers are lost when a sequence is aged out of the shared pool. DBMS_SHARED_POOL is useful for keeping sequences in the shared pool and thus preventing the loss of sequence numbers.

---

Operational Notes

To create DBMS_SHARED_POOL, run the DBMSPOOL.SQL script. The PRVTPOOL.PLB script is automatically executed after DBMSPOOL.SQL runs. These scripts are not run by as part of standard database creation.

Summary of DBMS_SHARED_POOL Subprograms

---

ABORTED_REQUEST_THRESHOLD Procedure

This procedure sets the aborted request threshold for the shared pool.

Syntax

DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD (
   threshold_size NUMBER);

Parameters

Exceptions

An exception is raised if the threshold is not in the valid range.

Usage Notes

Usually, if a request cannot be satisfied on the free list, then the RDBMS tries to reclaim memory by freeing objects from the LRU list and checking periodically to see if the request can be fulfilled. After finishing this step, the RDBMS has performed a near equivalent of an 'ALTER SYSTEM FLUSH SHARED_POOL'.

Because this impacts all users on the system, this procedure "localizes" the impact to the process failing to find a piece of shared pool memory of size greater than thresh_hold size. This user gets the 'out of memory' error without attempting to search the LRU list.

---

KEEP Procedure

This procedure keeps an object in the shared pool. Once an object has been kept in the shared pool, it is not subject to aging out of the pool. This may be useful for frequently used large objects. When large objects are brought into the shared pool, several objects may need to be aged out to create a contiguous area large enough.

Syntax

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

Parameters

Exceptions

An exception is raised if the named object cannot be found.

Usage Notes

There are two kinds of objects:

• PL/SQL objects, triggers, sequences, and types which are specified by name

• SQL cursor objects which are specified by a two-part number (indicating a location in the shared pool).

For example:

DBMS_SHARED_POOL.KEEP('scott.hispackage') 

This keeps package HISPACKAGE, owned by SCOTT. The names for PL/SQL objects follow SQL rules for naming objects (for example, delimited identifiers and multibyte names are allowed). A cursor can be kept by DBMS_SHARED_POOL.KEEP('0034CDFF, 20348871','C'), 0034CDFF being the ADDRESS and 20348871 the HASH_VALUE. Note that the complete hexadecimal address must be in the first 8 characters.

---

PURGE Procedure

This procedure purges the named object or specified heap(s) of the object.

Syntax

DBMS_SHARED_POOL.PURGE (
   name    VARCHAR2, 
   flag    CHAR DEFAULT 'P', 
   heaps   NUMBER DEFAULT 1)

Parameters

Exceptions

ORA-6570: An exception is raised if the named object cannot be found

ORA-6570: An object cannot be purged it marked as permanently kept

Usage Notes

All objects supported by the KEEP Procedure are supported for PURGE.

---

SIZES Procedure

This procedure shows objects in the shared_pool that are larger than the specified size. The name of the object is also given, which can be used as an argument to either the KEEP or UNKEEP calls.

Syntax

DBMS_SHARED_POOL.SIZES ( 
   minsize NUMBER);

Parameters

Usage Notes

Issue the SQLDBA or SQLPLUS 'SET SERVEROUTPUT ON SIZE XXXXX' command prior to using this procedure so that the results are displayed.

---

UNKEEP Procedure

This procedure unkeeps the named object.

Syntax

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

Parameters

Exceptions

An exception is raised if the named object cannot be found.