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

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle


Donald K. Burleson

Oracle Tips

Oracle Shared Pool

The shared pool is the most important area of the SGA, except for the data buffer caches. There are a number of sub-areas within the SGA, each with its own important purpose. Unfortunately, all of the sub-areas are controlled by the single shared_pool_size parameter.   A shortage of shared pool RAM may result in high library cache reloads, high row cache reloads, and shared pool latch contention. You may also see the error: "ORA-04031: Out of shared pool memory".

For complete details on Oracle shared pool memory, see my master book "Oracle Tuning: The Definitive Reference".

Since it is not possible to dedicate separate regions of memory for the shared pool components, the shared pool is usually the second-largest SGA memory area (depending on the size of the db_cache_size parameter). The shared pool contains RAM memory regions that serve the following purposes:

Library cache – The library cache contains the current SQL execution plan information. It also holds stored procedures and trigger code.

Dictionary cache - The dictionary cache stores environmental information, which includes referential integrity, table definitions, indexing information, and other metadata stored within Oracle's internal tables.

Session information – Systems that use SQL*Net version 2 with a multi-threaded server need this area to store session information. Beginning with Oracle, the v$session view contains information related to Oracle*Net users.

Also, see my other notes on the Oracle shared pool:


The above text is an excerpt from "Creating a Self Tuning Oracle Database", by Rampant TechPress.  It is only $9.95 and all scripts in this tips can be immediately downloaded.


Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books

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

Oracle® is the registered trademark of Oracle Corporation.