Pinning the object

By default, the RDBMS handles all loading and flushing of SQL information from the shared buffer pool. The database uses a least-recently-used algorithm to prioritize shared pool memory. Database objects are loaded into the shared pool (in compiled pcode form) the first time they are referenced and are immediately available for removal once all references to that object have finished. Pinning database objects into the SGA shared pool is very important to tuning large TPO-based applications. Database packages, functions, procedures, and individual cursors can all be pinned into memory. For example:

PACKAGE dbms_shared_pool IS
PROCEDURE Keep (pkg_name varchar2);
END;
sql >> execute dbms_shared_pool.keep ('scott.GL_package');
When a database object is pinned into memory and it is not already in the shared pool, the object is queued to be kept when it is first referenced. Note that the keep function does not compile and load the object into the SGA.

Note: To permit an object to be pinned into memory, EXECUTE privilege must be granted to the Oracle user account that owns the DBMS_SHARED_POOL package (normally SYS). This is necessary because packages and procedures are executed with the privileges of their creator, not the initiator.

Some users pin objects in the SGA in a preemptive way before they are actually loaded. Doing this can overshadow any possible gains. Once the object is referenced, the responsible session will bear the cost of loading and compiling the object. In the case of very large packages and procedures, this can be several minutes. Even worse, the object may not be able to be loaded because of insufficient or checkered memory within the shared buffer pool. We recommend that you always load (reference) an object before pinning it into the SGA. This is best done on database startup by the startup routine itself. How do you reference a package or procedure without executing it? You can't. What happens if the object updates the database or deletes rows from a table? Our preference is to pin only packages into memory and include a dummy variable definition in every package that can be referenced safely.