Commonly used database procedures and packages are cached in an area of the Oracle shared pool known as the Library Cache. The caching reduces the need for constant reads from datafiles and improves performance. However, over time, if these program units are not utilized, Oracle will gradually remove them from the library cache according to a "Least Recently Used" (LRU) algorithm.
Once a program unit is removed from the library cache, any subsequent attempt to access it will require a reload from a disk which is much slower than a reload from cached memory. While this may be acceptable in some circumstances, in many cases it will result in noticable decreased performance.
For example, many users complain of a lag on initial access of Banner® Self-Service Web pages. However, subsequent pages accessed are returned quickly. This "initial lag" is usually the result of the Oracle having to reload the main web toolkit packages, HTP and HTF into memory from disk. Once loaded, they become cached and are retrieved from memory on subsequent loads.
To prevent certain program units from being removed from the library cache, Oracle provides a package called DBMS_SHARED_POOL. This package can be installed into the database by running the script dbmspool.sql located in the $ORACLE_HOME/rdbms/admin directory as the SYS user.
Once this package is created in the database, any other package or procedure can be "Pinned" into the library cache. Objects that are "pinned" will not be removed regardless of whether they are accessed.
To pin an object, execute the KEEP procedure within the DBMS_SHARED_POOL package passing in the procedure/package name to be pinned as the argument. For example, the command below will pin the SYS.HTP package into the library cache.
SQL> execute sys.dbms_shared_pool.keep('SYS.HTP');
To improve performance, Oracle usually recommends that the following objects should be pinned.
SYS.DBMS_PIPE
SYS.STANDARD
SYS.DBMS_STANDARD
SYS.DBMS_UTILITY
SYS.DBMS_DESCRIBE
SYS.DBMS_OUTPUT
SYS.DIUTIL
SYS.WPIUTL
SYS.HTP
SYS.HTF
SYS.OWA_UTIL
SYS.WPG_DOCLOAD
These packages can all be pinned using a procedure PROC_PKGS_KEEP that calls the DBMS_SHARED_POOL package.
CREATE OR REPLACE PROCEDURE proc_pkgs_keep AS
BEGIN
sys.dbms_shared_pool.keep('SYS.DBMS_PIPE');
sys.dbms_shared_pool.keep('SYS.STANDARD');
sys.dbms_shared_pool.keep('SYS.DBMS_STANDARD');
sys.dbms_shared_pool.keep('SYS.DBMS_UTILITY');
sys.dbms_shared_pool.keep('SYS.DBMS_DESCRIBE');
sys.dbms_shared_pool.keep('SYS.DBMS_OUTPUT');
sys.dbms_shared_pool.keep('SYS.DIUTIL');
sys.dbms_shared_pool.keep('SYS.WPIUTL');
sys.dbms_shared_pool.keep('SYS.HTP');
sys.dbms_shared_pool.keep('SYS.HTF');
sys.dbms_shared_pool.keep('SYS.OWA_UTIL');
sys.dbms_shared_pool.keep('SYS.WPG_DOCLOAD');
END;
/
Running this procedure will then pin all the packages listed above into the library cache.
Once pinned, objects will stay in the library cache until the database is shutdown. After an instance shutdown/restart, all packages/procedures need to be re-pinned. To automate this process, create a trigger that executes PROC_PKGS_KEEP on instance startup.
CREATE OR REPLACE TRIGGER db_startup_pin AFTER STARTUP ON DATABASE
BEGIN
sys.proc_pkgs_keep;
END;
/
You can query the objects that are currently pinned in the Shared Pool by running the following query:
SELECT owner, name, type, kept
FROM v$db_object_cache WHERE kept='YES';
Finally, keep in mind that permanently pinning objects into the shared pool does cost memory. Thus you will need to monitor the library cache performance and increase the SHARED_POOL_SIZE parameter in the INIT.ORA file if needed.
