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.
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:
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
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 diagram below will help you review the components of the shared pool.
Shared Pool Components
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.
This stores environmental information, including referential integrity, table definitions, indexing information, and other metadata stored within Oracle's internal tables.
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.
1) SHAREDPOOL 2) REDO LOG BUFFER 3) DATABASE BUFFER CACHE
Components of the shared pool 1) SESSION MEMORY 2) LIBRARY CACHE 3) DICTIONARY CACHE
The main components of the shared pool are the library cache, the dictionary cache, and, depending on your configuration, the server result cache. The library cache stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code. The dictionary cache stores data referenced from the data dictionary. The server result cache stores the results of queries and PL/SQL function results. Many of the caches in the shared pool automatically increase or decrease in size, as needed, including the library cache and the dictionary cache. Old entries are aged out to accommodate new entries when the shared pool does not have free space.
A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache. For this reason, the shared pool should be sized to ensure that frequently used data is cached. Several features make large memory allocations in the shared pool: for example, the shared server, parallel query, or Recovery Manager. Oracle recommends segregating the SGA memory used by these features by configuring a distinct memory area, called the large pool. Allocation of memory from the shared pool is performed in chunks. This chunking enables large objects (over 5 KB) to be loaded into the cache without requiring a single contiguous area. In this way, the database reduces the possibility of running out of enough contiguous memory due to fragmentation. Infrequently, Java, PL/SQL, or SQL cursors may make allocations out of the shared pool that are larger than 5 KB. To allow these allocations to occur most efficiently, Oracle Database segregates a small amount of the shared pool.
This memory is used if the shared pool does not have enough space. The segregated area of the shared pool is called the reserved pool.
The next lesson will examine tuning the shared pool for optimal performance.
