Shared Pool   «Prev  Next»
Lesson 10

Tuning Shared Pool Conclusion

This module has focused on tuning the Oracle shared pool. We have looked closely at broader tuning issues and those specifically related to the library cache. Now 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

Automatic Shared Memory Management

Oracle Database 10g introduced Automatic Shared Memory Management, while Oracle Database 11g added Automatic Memory Management for the SGA and PGA instance components. Whenever the MEMORY_TARGET (new to Oracle Database 11g) or SGA_TARGET initialization parameter is set, the database automatically distributes the memory among various SGA components providing optimal memory management. The shared memory components automatically sized include the shared pool (manually set using SHARED_POOL_SIZE), the large pool (LARGE_POOL_SIZE), the Java pool (JAVA_POOL_SIZE), the buffer cache (DB_CACHE_SIZE), and the streams pool (STREAMS_POOL_SIZE). Automatic memory management initialization parameters can be set through Oracle Enterprise Manager.

The background processes interact with the operating system and each other to manage the memory structures for the instance. These processes also manage the actual database on disk and perform general housekeeping for the instance.
Figure 3-10 illustrates the memory structures and background processes discussed in the following section.
Figure 3-10: An Oracle instance

Additional background processes may exist when you use certain other features of the database; for example, shared servers (formerly the Multi-Threaded Server or MTS prior to Oracle9i), or job queues and replication.

New terms

Here are some terms from this module that may have been new to you:
  1. pinning packages: The process of keeping packages inside the library cache
  2. estat-bstat utility: Oracle begin statistics (utlbstat.sql) and end statistics (utlestat.sql). These are used to create an elapsed-time snapshot of Oracle activity.
  3. shared SQL area: The shared SQL area is used to keep and process SQL statements and PL/SQL code.
  4. library cache miss ratio: Represents the ratio of the sum of library cache reloads to the sum of pins.
In the next module, we will look at tuning the Oracle buffer cache.

Shared Pool Concepts - Quiz

Before you go on, click the Quiz link below to complete one last quiz for this module.
Shared Pool Concepts - Quiz