An important region of the SGA is the shared pool, which stores a number of sub-areas in memory.
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:
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
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.
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:
Parse time is avoided if the SQL statement is already in the shared pool. This saves CPU resources.
Application memory overhead is reduced, since all applications use the same pool of shared SQL statements and dictionary resources.
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.