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:
List shared pool tuning techniques
Pin packages in the shared pool
Reserve space for large objects with shared_pool_reserved size
Find high-use packages
Gather information about the number of reloads required
Detect library cache contention
Determine library cache misses and parse calls for SQL
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:
pinning packages: The process of keeping packages inside the library cache
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.
shared SQL area: The shared SQL area is used to keep and process SQL statements and PL/SQL code.
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