147 DBMS_SHARED_POOL
The DBMS_SHARED_POOL
package provides access to the shared pool, which is the shared memory area where cursors and PL/SQL objects are stored. DBMS_SHARED_POOL
enables you to display the sizes of objects in the shared pool, and mark them for keeping or not-keeping in order to reduce memory fragmentation.
This chapter contains the following topics:
147.1 DBMS_SHARED_POOL 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.
147.2 DBMS_SHARED_POOL 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.
147.3 Summary of DBMS_SHARED_POOL Subprograms
This table lists the DBMS_SHARED_POOL
subprograms and briefly describes them.
Table 147-1 DBMS_SHARED_POOL Package Subprograms
Subprogram | Description |
---|---|
Sets the aborted request threshold for the shared pool |
|
Keeps an object in the shared pool |
|
Marks a library cache object as a hot object |
|
Purges the named object or specified heap(s) of the object |
|
Shows objects in the shared pool that are larger than the specified size |
|
Unkeeps the named object |
|
Unmarks a library cache object as a hot object |
147.3.1 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
Table 147-2 ABORTED_REQUEST_THRESHOLD Procedure Parameters
Parameter | Description |
---|---|
|
Size, in bytes, of a request which does not try to free unpinned (not "unkeep-ed") memory within the shared pool. The range of |
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.
147.3.2 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
Table 147-3 KEEP Procedure Parameters
Parameter | Description |
---|---|
|
Name of the object to keep. The value for this identifier is the concatenation of the address and Currently, |
|
(Optional) If this is not specified, then the package assumes that the first parameter is the name of a package/procedure/function and resolves the name. Set to Set to Set to Set to Set to |
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.
147.3.3 MARKHOT Procedure
This procedure marks a library cache object as a hot object.
Syntax
DBMS_SHARED_POOL.MARKHOT ( schema VARCHAR2, objname VARCHAR2, namespace NUMBER DEFAULT 1, global BOOLEAN DEFAULT TRUE); DBMS_SHARED_POOL.MARKHOT ( hash VARCHAR2, namespace NUMBER DEFAULT 1, global BOOLEAN DEFAULT TRUE);
Parameters
Table 147-4 MARKHOT Procedure Parameters
Parameter | Description |
---|---|
|
User name or the schema to which the object belongs |
|
Name of the object |
|
Number indicating the library cache namespace in which the object is to be searched. Views, such as |
|
If |
|
16-byte hash value for the object |
Exceptions
ORA-06502
: An exception is raised if the named object cannot be found due to incorrect input
ORA-04043
: An exception is raised if the named object cannot be found (bad namespace, or hash input)
Usage Notes
If a package or type's specification is marked hot or unhot, then the corresponding package or type body will be implicitly marked as hot or unhot.
147.3.4 PURGE Procedure
This procedure purges the named object or specified heaps of the object.
Syntax
DBMS_SHARED_POOL.PURGE ( name VARCHAR2, flag CHAR DEFAULT 'P', heaps NUMBER DEFAULT 1); DBMS_SHARED_POOL.PURGE ( schema VARCHAR2, objname VARCHAR2, namespace NUMBER, heaps NUMBER); DBMS_SHARED_POOL.PURGE ( hash VARCHAR2, namespace NUMBER, heaps NUMBER);
Parameters
Table 147-5 PURGE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the object to purge. The value for this identifier is the concatenation of the address and Currently, |
|
(Optional) If this is not specified, then the package assumes that the first parameter is the name of a package/procedure/function and resolves the name. Set to 'P' or 'p' to fully specify that the input is the name of a package/procedure/function. Set to 'T' or 't' to specify that the input is the name of a type. Set to 'R' or 'r' to specify that the input is the name of a trigger. Set to 'Q' or 'q' to specify that the input is the name of a sequence. In case the first argument is a cursor address and hash-value, the parameter should be set to any character except 'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or 'T' or 't'. |
|
Heaps to be purged. For example, if heap 0 and heap 6 are to be purged: 1<<0 | 1<<6 => hex 0x41 => decimal 65, so specify heaps =>65.Default is 1, that is, heap 0 which means the whole object would be purged |
|
User name or the schema to which the object belongs |
|
Name of the object to purge |
|
Parameter is a number indicating the library cache namespace in which the object is to be searched |
|
16-byte hash value for the object |
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
.
147.3.5 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
Table 147-6 SIZES Procedure Parameters
Parameter | Description |
---|---|
|
Size, in kilobytes, over which an object must be occupying in the shared pool, in order for it to be displayed. |
Usage Notes
Issue the SQLDBA or SQLPLUS 'SET
SERVEROUTPUT
ON
SIZE
XXXXX'
command prior to using this procedure so that the results are displayed.
147.3.6 UNKEEP Procedure
This procedure unkeeps the named object.
Syntax
DBMS_SHARED_POOL.UNKEEP ( name VARCHAR2, flag CHAR DEFAULT 'P');
WARNING:
This procedure may not be supported in the future if automatic mechanisms are implemented to make this unnecessary.
Parameters
Table 147-7 UNKEEP Procedure Parameters
Parameter | Description |
---|---|
|
Name of the object to unkeep. See description of the |
|
See description of the flag parameter for the |
Exceptions
ORA-06502
: An exception is raised if the named object cannot be found
147.3.7 UNMARKHOT Procedure
This procedure unmarks a library cache object as a hot object.
Syntax
DBMS_SHARED_POOL.UNMARKHOT ( schema VARCHAR2, objname VARCHAR2, namespace NUMBER DEFAULT 1, global BOOLEAN DEFAULT TRUE); DBMS_SHARED_POOL.UNMARKHOT ( hash VARCHAR2, namespace NUMBER DEFAULT 1, global BOOLEAN DEFAULT TRUE);
Parameters
Table 147-8 UNMARKHOT Procedure Parameters
Parameter | Description |
---|---|
|
User name or the schema to which the object belongs |
|
Name of the object |
|
Number indicating the library cache namespace in which the object is to be searched |
|
If |
|
16-byte hash value for the object |
Exceptions
ORA-06502
: An exception is raised if the named object cannot be found due to incorrect input
ORA-04043
: An exception is raised if the named object cannot be found (bad namespace, or hash input, or non-existent object)
Usage Notes
If a package or type's specification is marked hot or unhot, then the corresponding package or type body will be implicitly marked as hot or unhot.