DBMS_SHARED_POOL enables you to display the sizes of objects in the shared pool, and mark them for keeping or unkeeping in order to reduce memory fragmentation.
--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.
Table 97-1 DBMS_SHARED_POOL Package Subprograms
Subprogram Description
ABORTED_REQUEST_THRESHOLD Procedure
Sets the aborted request threshold for the shared pool
KEEP Procedure
Keeps an object in the shared pool
SIZES Procedure
Shows objects in the shared pool that are larger than the specified size
UNKEEP Procedure
Unkeeps the named object
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 97-3 KEEP Procedure Parameters
Parameter Description
name Name of the object to keep.
The value for this identifier is the concatenation of the address and hash_value columns from the v$sqlarea view. This is displayed by the SIZES procedure.
Currently, TABLE and VIEW objects may not be kept.
flag (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.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24799772/viewspace-681365/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24799772/viewspace-681365/