Oracle - DBMS_SHARED_POOL
Oracle uses LRU algorithm to age and remove least recently used packages and other PL/SQL objects from shared memory pool. When a procedure or function from a package is called, the package gets loaded into the shared memory pool, which can take some time and cause momory fragmenatation. Pinning a large package into shared memory pool is by executing the procedure keep.
exec DBMS_SHARED_POOL.keep(object_name, flag);
The flag values are
P or p - package/procedure/function (default value),
T or t - Type
R or r - Trigger and
Q or q - Sequence.
By pinning a package into the shared memory, it does not get aged out due to drop in usage or if the shared pool gets full, resulting in improved PL/SQL performance by reducing memory fragmentation. The procedure unkeep will remove the PL/SQL object from the shared memory pool.
If the object_name is a cursor address and hash-value, then the flag value should be any value other than P,p,R,r,T,t,Q,q.
 
The DBMS_SHARED_POOL package is not created by default and has to be loaded by DBA by executing DBMSPOOL.SQL script.

Oracle registered trademark of Oracle Corporation.

Last Revised on: February 23, 2012

  1429