Oracle8i Supplied PL/SQL Packages Reference
Release 2 (8.1.6)

Part Number A76936-01

Library

Product

Contents

Index

Go to previous page Go to beginning of chapter Go to next page

DBMS_SHARED_POOL, 2 of 2


Summary of Subprograms

Table 47-1 DBMS_SHARED_POOL Package Subprograms
Subprogram  Description 
SIZES Procedure
 

Shows objects in the shared pool that are larger than the specified size 

KEEP Procedure
 

Keeps an object in the shared pool 

UNKEEP Procedure
 

Unkeeps the named object 

ABORTED_REQUEST_THRESHOLD 
Procedure
 

Sets the aborted request threshold for the shared pool 

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

Syntax

DBMS_SHARED_POOL.SIZES ( 
   minsize NUMBER);

Parameters

Table 47-2 SIZES Procedure Parameters
Parameter  Description 
minsize
 

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.

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 certain semi-frequently used large objects, because when large objects are brought into the shared pool, a larger number of other objects (much more than the size of the object being brought in) may need to be aged out in order to create a contiguous area large enough.

Syntax

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


Note:

This procedure may not be supported in the future if automatic mechanisms are implemented to make this unnecessary. 


Parameters

Table 47-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 keeped. 

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.

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

Exceptions

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

Usage Notes

There are two kinds of objects:

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 (i.e., delimited identifiers, multi-byte names, etc. are allowed). A cursor can be keeped by DBMS_SHARED_POOL.KEEP('0034CDFF, 20348871'). The complete hexadecimal address must be in the first 8 characters.

UNKEEP Procedure

This procedure unkeeps the named object.

Syntax

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


Caution:

This procedure may not be supported in the future if automatic mechanisms are implemented to make this unnecessary. 


Parameters

Table 47-4 UNKEEP Procedure Parameters
Parameter  Description 
name
 

Name of the object to unkeep. See description of the name object for the KEEP procedure. 

flag
 

See description of the flag parameter for the KEEP procedure. 

Exceptions

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

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 47-5 ABORTED_REQUEST_THRESHOLD Procedure Parameters
Parameter  Description 
threshold_size
 

Size, in bytes, of a request which does not try to free unpinned (not "unkeep-ed") memory within the shared pool. The range of threshold_size is 5000 to ~2 GB inclusive.  

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.


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index