[ORACLE] Pinnig Objects

Pinning:
- Is used s that objects avoid the Oracle least recently used (LRU) mechanism and do not get flushed out of memory.
- Is applied with the help of the
sys.dbms_shared_pool package:
-> sys.dbms_shared_pool.keep
-> sys.dbms_shared_pool.unkeep
-> sys.dbms_shared_pool.sizes

What is Pinning a Package?
Sizing the shared pool properly is on of the ways of ensuring that frequently used objects are available in memory whenever needed. So that performance improves. Another way to improve performance is to pin frequently used packages in the shared pool.

When a package is pinned, it is not aged out with the normal least recently used (LRU) mechanism that the Oracle server otherwise used to flush out a least recently used package. The package remains in memory no matter how full the shared pool gets or how frequently you access the package.

To create DBMS_SHARED_POOL, run the DBMMSPOOL.SQL

Syntax:
sys.dbms_shared_pool.keep(object_name, flag)
sys.dbms_shared_pool.unkeep(object_name, flag)

Syntax Definitions:
where: object_name => Name of the object to keep
flag => 'P' or 'p' package/procedure/function This is the default , 'T' or 't' type, 'R' or 'r' trigger

Example:
EXECUTE dbms_shared_pool.keep ('XXX.BB', 'P');
EXECUTE dbms_shared_pool.keep ('XXX.BABE', 'P');

remark:

If you have used Oracle RAC, you should use Pin command on every nodes.
and you can check:

SQL> select INST_ID, NAME, TYPE ,KEPT FROM gv$db_object_cache WHERE NAME = 'BB';

INST_ID NAME TYPE KEP
---------- ----- ---------------------------- ---
2 BB PACKAGE YES
2 BB PACKAGE BODY YES
3 BB PACKAGE YES
3 BB PACKAGE BODY YES
4 BB PACKAGE YES
4 BB PACKAGE BODY YES
1 BB PACKAGE YES
1 BB PACKAGE BODY YES


.