How to Keep / Pin / Check PLSQL Objects in Buffer Cache / Memory Using DBMS_SHARED_POOL.KEEP


The Eucharistic Miracles of the World
It will take time to load huge packages to the memory because the smaller objects needs to be moved out of shared pool to make a room for the new big plsql object. To avoid these kind of delays you can keep the PLSQL codes like procedure, function, etc permanently using the DBMS_SHARED_POOL system package. This package allows you to keep the sequences also in the shared pool which will avoid the sequence number missing situation.

   name VARCHAR2,
   flag CHAR      DEFAULT 'P');
Where name is the object name and
flag is the type of the object.
Flag values can be
     P/p: for procedure/function/package
     T/t: for Type
     R/r: For Trigger
     Q/q: For Sequence
     the first argument is a cursor address and hash-value, the parameter can be set to any character except 'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or 'T' or 't'.

See EXAMPLE how to keep a simple procedure in shared pool
SQL> create or replace function listNum (a number) return number is
return 1;
Function created.

SQL> exec sys.DBMS_SHARED_POOL.KEEP('scott.listNum');

PL/SQL procedure successfully completed.

You can check v$db_object_cache dictionary table whether the object is in buffer pool or not.
SQL> select kept, name from v$db_object_cache
where name ='LISTNUM';

--- ------------------------------
You can remove a object from SHARE POOL using UNKEEP function
SQL> exec sys.DBMS_SHARED_POOL.unkeep('scott.listNum');

PL/SQL procedure successfully completed.

SQL> select kept, name from v$db_object_cache where name ='LISTNUM';

--- ------------------------------


Website Stats


Post a Comment


Oracle (580) General (60) Unix (47) Script (41) Blog (23) OCI (3) SQL* Loader (3) Datapump (2) Software (1)
DBA Tips Copyright 2011-21 All Rights Reserved | Site Map | Contact | Disclaimer