RelationalDBDesign RelationalDBDesign

Oracle Instance  «Prev  Next»
Lesson 3 Tuning considerations of the shared pool
Objective Describe the operation of the shared pool.

Tuning considerations of Shared Pool

An important region of the SGA is the shared pool, which stores a number of sub-areas in memory.

Shared pool consisting of 1) Session Memory 2) Library Cache 3) Dictionary Cache

The shared pool is normally the second largest memory structure within the SGA, depending upon the size of the db_block_size parameter. The shared pool holds memory for the following purposes:

Library cache

The library cache stores the plan information for SQL that is currently being executed. This area also holds stored procedures and trigger code. The SQL areas within the library cache hold SQL so that a repeated SQL statement does not need to be re-parsed each time that the SQL is processed.

Data dictionary cache

The data dictionary cache stores environmental information, including referential integrity, table definitions, indexing information, and other metadata stored within Oracle's internal tables.
When a package is invoked, Oracle first checks the dictionary cache to see if the package is already in memory. This eliminates disk access and therefore performance of SQL statements improves. Later in the course, you will learn to query the dictionary cache to compare dictionary cache hits to misses.

Session information

The session information area stores session information for systems that are using Oracle Net Services with Oracle Shared Server. For systems that do not use Oracle Shared Server, this area will not use much space.

Sizing the Shared Pool

One important aspect of instance tuning that you will learn in this course is proper sizing of the shared pool. This can reduce resource consumption in at least three ways:
  1. Parse time is avoided if the SQL statement is already in the shared pool. This saves CPU resources.
  2. Application memory overhead is reduced, since all applications use the same pool of shared SQL statements and dictionary resources.
  3. I/O resources are saved, since dictionary elements which are in the shared pool do not require disk access.
One of the confounding problems with Oracle is that all of these sub-areas are sized by only one init.ora parameter called shared_pool_size. It is impossible to dedicate separate regions of memory for the components within the shared pool.
In the following lessons, you will get an overview of the tuning issues involved in each of the major components of the shared pool, the library cache, the data dictionary cache and the session information area. Let bus egin with the library cache.