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