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

advertisements

_____________________________________________________________________________________________________________________

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.


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

_____________________________________________________________________________________________________________________

A visitor from Nagpur viewed 'RMAN-08591: WARNING: invalid archived log deletion' 1 hr 5 mins ago
A visitor from Volgograd viewed 'Using vi, awk, sed - erase / remove / cut Few Firs' 1 hr 43 mins ago
A visitor from Cuenca viewed 'Stop / Drop Scheduled / Running jobs in Oracle' 2 hrs 8 mins ago
A visitor from Maryland viewed 'Fix ORA-01927: cannot REVOKE privileges you did no' 3 hrs 43 mins ago
A visitor from Iowa viewed 'Xiaomi Pad 6: A Feature-Rich Tablet at an Affordab' 4 hrs 47 mins ago

0 comments:

Post a Comment

Labels

Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2)
 

acehints.com Copyright 2011-25 All Rights Reserved | Site Map | Contact | Disclaimer