Shared Pool   «Prev  Next»
Lesson 1

Shared Pool Tuning in Oracle

Now that you have a broad understanding of how
  1. the dictionary cache,
  2. the library cache, and
  3. the UGA session memory
affect the efficiency of the shared pool, the next topic to discuss is tuning the shared pool.
This module discusses how to tune the shared pool.
Because the library cache plays such an integral role in shared pool tuning, much of this module is devoted to exploring the numerous considerations of tuning via the library cache.
The MouseOver below will help you review the components of the shared pool.

Session Memory, Library Cache, Dictionary Cache
  1. This 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 in the hope that a repeated SQL statement would not need to be re-parsed each time the SQL is processed.
  2. This stores environmental information, including referential integrity, table definitions, indexing information, and other metadata stored within Oracle's internal tables.
  3. This stores session information for systems that are using SQL*Net or Net8 with Oracle's multithreaded server. For systems that do not use the MTS, this area will not use much space.

Shared Pool Components
In this module, you will get an in-depth overview of the Oracle shared pool and learn to recognize and correct problems within all components of the shared pool. By the time you complete this module, you should be able to:
  1. List shared pool tuning techniques
  2. Pin packages in the shared pool
  3. Reserve space for large objects with shared_pool_reserved size
  4. Find high-use packages
  5. Gather information about the number of reloads required
  6. Detect library cache contention
  7. Determine library cache misses and parse calls for SQL
  8. Understand SQL re-use in the library cache
    Finally, at the completion of this module you will have the knowledge (and scripts) that will allow you to quickly investigate shared pool issues.
The next lesson will examine tuning the shared pool for optimal performance.