Diving Into the Shared Pool - An In Depth Look at Tuning the Shared Pool (Part 2) Oracle Tips by Mike Ault

What to Pin

The purpose of the null startup function is to provide a touch point to pull the entire package into the shared pool. This allows you to create a startup SQL procedure that pulls all of the application packages into the pool and pins them using the DBMS_SHARED_POOL package. The DBMS_SHARED_POOL package may have to be built in earlier releases of Oracle. The DBMS_SHARED_POOL package is built using the DBMSPOOL.SQL and PRVTPOOL.PLB scripts located in (UNIX) $ORACLE_HOME/rdbms/admin or (NT) x:\orant\rdbms\admin  (where x: is the home drive for your install).

How do you determine what packages, procedures of functions to pin? Actually, Oracle has made this easy by providing the V$DB_OBJECT_CACHE view that shows all objects in the pool, and, more importantly, how they are being utilized. The script in Figure 2 provides a list of objects that have been loaded more than once and have executions greater than one. Some example output from this script is shown in figure 16. A rule of thumb is that if an object is being frequently executed and frequently reloaded it should be pinned into the shared pool.

SEE CODE DEPOT FOR FULL SCRIPTS

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.