PINNING IN THE SHARED POOL

PINNING IN THE SHARED POOL

The shared pool is the portion of the SGS that contains, among other things, the p-code of compiled subprograms as they are run. The first time a stored a store subprogram is called, the p-code is loaded from disk into the shared pool. Once the object is no longer referenced, it is free to be aged out. Objects are aged out of the shared pool using an LRU(Least Recently Used) algorithm.

The DBMS_SHARED_POOL package allows you to pin objects in the shared pool. When an object is pinned, it will never be aged out until you request it, no matter how full the pool gets or how often the object is accessed. This can improve performance, as it takes time to reload a package from disk.

DBMS_SHARED_POOL has four procedures

Ø  KEEP

Ø  UNKEEP

Ø  SIZES

Ø  ABORTED_REQUEST_THRESHOLD

KEEP

The DBMS_SHARED_POOL.KEEP procedure is used to pin objects in the pool.

Syntax:

            PROCEDURE KEEP(object_name varchar2,flag char default ‘P’);

Here the flag represents different types of flag values for different types of objects.

P          --         Package, function or procedure

Q         --         Sequence

R          --         Trigger

C          --         SQL Cursor

T          --         Object type

JS        --         Java source

JC        --         Java class

JR        --         Java resource

JD        --         Java shared data

UNKEEP

UNKEEP is the only way to remove a kept object from the shared pool, without restarting the database. Kept objects are never aged out automatically.

Syntax:

            PROCEDURE UNKEEP(object_name varchar2, flag char default ‘P’);

SIZES

SIZES will echo the contents of the shared pool to the screen.

Syntax:

            PROCEDURE SIZES(minsize number);

Objects with greater than the minsize will be returned. SIZES uses DBMS_OUTPUT to return the data.

ABORTED_REQUEST_THRESHOLD

When the database determines that there is not enough memory in the shared pool to satisfy a given request, it will begin aging objects out until there is enough memory. It enough objects are aged out, this can have a performance impact on other database sessions. The ABORTED_REQUEST_THRESHOLD can be used to remedy this.

Syntax:

            PROCEDURE ABORTED_REQUEST_THRESHOLD(threshold_size number);

Once this procedure is called, oracle will not start aging objects from the pool unless at least threshold_size bytes is needed.