advertisements
_____________________________________________________________________________________________________________________
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
--- ------------------------------
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment