Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Pinning PL/SQL Packages in the Shared Pool

Oracle Tips by Burleson Consulting


The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

The initial call to a package causes the whole package to be loaded into the shared pool.  For large packages this may represent an unacceptable delay for two reasons.  First the size of the package causes a delay in load time.  Second if the shared pool is already full, several smaller objects may need to be aged out to make room for it.  In these circumstances, performance can be improved by pinning large packages in the shared pool.

Under normal circumstances, objects in the shared pool are placed on a least recently used (LRU) list.  If the shared pool is full and a new object must be loaded, items on the LRU list are aged out.  Subsequent calls to objects that have been aged out result in them being reloaded into the shared pool. 

The processes of pinning objects in the shared pool removes them from the LRU list so they are no longer available to be aged out, regardless of usage.  The process of pinning objects in the shared pool is achieved using the dbms_shared_pool package.

The dbms_shared_pool package is not loaded by default, so it must be loaded manually by running the dbmspool.sql script as the SYS user.

SQL> conn sys/password as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql

Package created.

Grant succeeded.

View created.

Package body created.

Pinning and unpinning objects in the shared pool is achieved using the keep and unkeep procedures, both of which accept the same case-insensitive parameters.

  name  VARCHAR2,
  flag  CHAR      DEFAULT 'P'

PROCEDURE unkeep (
  name  VARCHAR2,
  flag  CHAR      DEFAULT 'P'

The name parameter is used to specify the object to be pinned or unpinned. The flag parameter specifies the object type, of which the following are valid:

  • P - Package, Procedure or Function.  This is the default value.

  • T - Type.

  • R - Trigger.

  • Q - Sequence.

  • C Cursor. The current documentation does not list C as a valid parameter and suggests that for cursors any parameter can be used.  In practice this does not appear to be true and the parameter value of C must be specified for cursors.

If the object to be pinned is a cursor, it should be identified using the address concatenated to the hash_value from the v$open_cursor view, as shown below.

SQL> conn sys/password as sysdba
SQL> SELECT address || ',' || hash_value FROM v$open_cursor WHERE rownum = 1;


1 row selected.

SQL> EXEC DBMS_SHARED_POOL.keep(6A524ABC,3792707131, C);
PL/SQL procedure successfully completed.

Only objects present in the shared pool can be pinned, so you must either wait for them to be loaded or force the load. 

The keep_test_api.sql script creates a package specification and body that will be used to demonstrate the keep and unkeep procedure usage.  The package contains a reference to a procedure called stub that performs no work.  A call to this procedure forces the package to be loaded into the shared pool so it can be pinned.




END keep_test_api;


-- -----------------------------------------------------------------
-- -----------------------------------------------------------------
END stub;
-- -----------------------------------------------------------------

END keep_test_api;

The following examples assume this package has been loaded into a schema called TEST, starting with the an example of the keep procedure.

SQL> conn sys/password as sysdba
SQL> EXEC test.keep_test_api.stub;

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SHARED_POOL.keep('test.keep_test_api');

PL/SQL procedure successfully completed.

To pin certain packages at instance startup, it makes sense to add an equivalent stub procedure to each of them, run a script to call the stubs and then pin the packages. 

To unpin the package, use the unkeep procedure shown below.

SQL> conn sys/password as sysdba
SQL> EXEC DBMS_SHARED_POOL.unkeep('test.keep_test_api');

PL/SQL procedure successfully completed.

Generally I do not pin objects in the shared pool, preferring instead to let Oracle manage the contents of the shared pool for me.  If the application is well written and the shared pool is sized correctly, there should be no need to resort to pinning.  In the event you are forced to work with badly written applications or in environments where memory is limited, some gains may be seen using this method.  Like all tuning, the key is to experiment in a controlled manner, constantly testing the results of the modifications.

The dbms_shared_pool package contains two more procedures that should be mentioned for the sake of completeness. 

The sizes procedure lists all objects present in the shared pool whose size in Kb exceeds the size specified in the procedure call.

SQL> EXEC DBMS_SHARED_POOL.sizes(minsize => 300);
------- ------ -------------------------------------------
470        SYS.STANDARD                  (PACKAGE)
390        SYS.DBMS_STATS                (PACKAGE BODY)
355        SYS.DBMS_RCVMAN               (PACKAGE BODY)

PL/SQL procedure successfully completed.

The aborted_request_threshold procedure can be used to prevent Oracle from flushing objects from the LRU list to make room for large objects. If there is insufficient free memory, an attempt to load an object whose size is above the threshold value will result in an ORA-4031 error.  The threshold can be set as follows.

SQL> EXEC DBMS_SHARED_POOL.aborted_request_threshold(40000);
PL/SQL procedure successfully completed.

A look back at the output from the dbmspool.sql script reveals that a view was created.  The name of that view was dba_keepsizes and is used to display the total space required by an object when it is kept in the shared pool.

  2  FROM   dba_keepsizes
  3  WHERE  owner = 'SYS'
  4  AND    name  = 'STANDARD';

   TOTSIZE OWNER                          NAME
---------- ------------------------------ --------------------------
       205 SYS                            STANDARD

1 row selected.

This allows the impact of pinning objects in the shared pool to be assessed prior to the operation taking place.

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational