Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips


Oracle Shared pool pinning

Using the above script, you can see how often an object has been loaded into the cache.  Many loads could indicate that the object is continuously being forced from the cache, which would potentially degrade performance. 

If the object is a code object such as a procedure, package, etc., you can pin the code in the cache to stop it from being removed.  In the above query, you can reference the kept column to see which code objects (if any) have already been pinned. 

The dbms_shared_pool package is used to pin or unpin code objects to and from the library cache.  For example, if you had a frequently referenced procedure called ERADMIN.ADD_ADMISSION, and you wanted to make sure that it would always be found in the library cache for quick reference, you would execute the following:

 exec sys.dbms_shared_pool .keep('ERADMIN.ADD_ADMISSION','P');

Performing a pin keeps the code where you want it at all times.  Pinned objects are also impervious to an alter system flush shared_pool command.

While this technique works well for code objects, what about regular SQL statements?  How can you keep them in the shared pool so that parse operations are minimized?  The easiest method is to ensure that user sessions are launching identical SQL statements, which allows reuse to occur in the cache. 

If Oracle detects that a user process has launched an identical SQL statement that is already present in the cache, it will reuse the statement rather than parse and load it into memory.  Using literals in SQL statements instead of bind variables can greatly hinder this process from occurring.  Again, the key to statement reuse is that the SQL has to be identical, and the use of literals in SQL statements can entirely negate this. 

Is there anything else you should look into with respect to the shared pool?  One other area of interest is the data dictionary cache.

The above is an excerpt from Oracle Performance Troubleshooting by Robin Schumacher.

It's only $19.95 and you can order it and get instant access to the Oracle scripts here:





Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.