Tuning the Shared Pool for Optimal Performance

Within the library cache, hit ratios can be determined for all dictionary objects that are loaded. These include table/procedures, triggers, indices, package bodies, and clusters. If any of the hit ratios fall below 75 percent, you should add to the shared_pool_size. Proper sizing of the shared pool can reduce resource consumption in at least three ways:

1. Parse time is avoided if the SQL statement is already in the shared pool. This saves CPU resources.
2. Application memory overhead is reduced, since all applications use the same pool of shared SQL statements and dictionary resources.
3. I/O resources are saved, since dictionary elements which are in the shared pool do not require disk access.

Setting cursor_space_for_time parameter to FALSE will accelerate executions within the library cache. This tells Oracle that a shared SQL area can be deallocated from the library cache to make room for a new SQL statement.
Setting cursor_space_for_time to TRUE means that all shared SQL areas are pinned in the cache until all application cursors are closed. When set to TRUE, Oracle will not bother to check the library cache on subsequent execution calls because it has already pinned the SQL in the cache. This technique can improve the performance for some queries, but cursor_space_for_time should not be set to TRUE if there are cache misses on execution calls. Cache misses indicate that the shared_pool_size is already too small, and forcing the pinning of shared SQL areas will only aggravate the problem.
Another way to improve performance in the library cache is to use the init.ora session_cached_cursors parameter.
In the next lesson, you will learn about pinning packages in the shared pool.

The main components of the shared pool are

1. the library cache,
2. the dictionary cache, and depending on your configuration,
3. 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.