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

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.


Syntax:
DBMS_SHARED_POOL.KEEP (
   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
begin
return 1;
end;
/
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';

KEP NAME
--- ------------------------------
YES 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';

KEP NAME
--- ------------------------------
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google