PINNING THE OBJECTS IN THE SHARED POOL

Shared Pool is a part of SGA which contains p-code of compiled subprograms.
First time a stored subprograms is called , p-code is loaded from the disk to the shared pool.
Once the object is no longer referenced, it is free to be aged out from the shared pool.

DBMS_SHARED_POOL package allows us to pin objects in the shared pool. When an object is pinned,
it will never be aged out until we request it.

* Pinning the objects can improve performance,as it takes time to reload a package from disk.
* Helps in minimizing fragmentation of shared pool.

DBMS_SHARED_POOL package has 4 procedures : KEEP
                                                                         UNKEEP
                                                                         SIZES
                                                                         ABORTED_REQUEST_THRESHOLD

KEEP :- This helps in pinning the objects in the shared pool
             PROCEDURE KEEP(name VARCHAR2,
                                      flag CHAR  DEFAULT 'P');
                                      name : Name of the object
                                      flag    : Determines the type of the object

UNKEEP :- If the object is pinned in the shared pool, only possible way to remove the object is to make use of procedure UNKEEP.
                PROCEDURE UNKEEP(name VARCHAR2,
                                              flag CHAR DEFAULT 'P');

SIZES :- This procedure will echo the contents of the shared pool to the screen.
            PROCEDURE SIZES(minsize NUMBER);
                                       minsize :object with a size greater that minsize will be returned. 

ABORTED_REQUEST_THRESHOLD :- When the database determines that there is not enough memory in the shared pool,
it will begin aging objects out until there is enough memory. If objects are aged out, this can have a performance impact on other
database sessions.

ABORTED_REQUEST_THRESHOLD can be used for remedy.
                                   PROCEDURE ABORTED_REQUEST_THRESHOLD(threshold_size NUMBER);

Once the procedure is called, Oracle will not start aging objects from the pool unless atleast threshold_size is needed.

*Terminologies/Words  marked in Blocked red color requires certain references for understanding.