|Oracle9i Supplied PL/SQL Packages and Types Reference
Release 2 (9.2)
Part Number A96612-01
DBMS_SHARED_POOL 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 unkeeping in order to reduce memory fragmentation.
This chapter discusses the following topics:
DBMS_SHARED_POOL, run the
SQL script. The
PLB script is automatically executed after
SQL runs. These scripts are not run by
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.