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

Looking into the Shared Pool

The four metrics below are revealed by the memsnap.sql query (after the buffer cache hit ratio), and concern the shared pool.  Execution response times can be adversely affected if Oracle has to handle parse activity, perform object definition lookups, or manage other code-related or reference tasks. 

The shared pool helps Oracle keep these reference-related activities to a minimum by holding SQL statements, along with code and object definitions, in memory.

As with the data cache, properly sizing the shared pool can be tricky and often involves trial and error.  The memsnap.sql query reveals that a shared pool that is sized too small has the following characteristics:

·        Zero or near-zero percent free in the pool after the database has only been up a short while

·        A library cache hit ratio that is below average (95% or below)

·        Many object reloads (due to definitions being forced from the pool prematurely)

·        A below average data dictionary cache hit ratio (95% or below)

The last three metrics mentioned above should be viewed in the same light as the buffer cache hit ratio, in that delta measurements often produce more meaningful results than cumulative measurements, and some databases will perform quite well with measures that appear non-optimal. 

With respect to the percent free in the shared pool, a near zero reading after the database has been up for some time is probably fine.  But, if the pool drops to zero free shortly after Oracle is started, you have a strong indication that it may be sized too small.

Oracle will always try and reuse SQL statements to keep from re-parsing a query, and while this can certainly reduce execution times when a shared pool is sized correctly, it can actually hinder progress when the pool is so large that Oracle wastes time interrogating it. 

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.