Tuning the Shared Pool for Optimal Performance

The shared pool is a critical component of the Oracle System Global Area (SGA). It stores shared memory structures such as shared SQL areas, dictionary cache, library cache, and server handles. Efficient shared pool management is crucial for optimizing database performance. Here are several shared pool tuning techniques in Oracle:

1. Size Appropriately: The initial step in shared pool tuning is sizing it properly. An undersized shared pool can lead to performance degradation, while an oversized one can waste memory resources. Monitor the library cache miss ratio, free memory in the shared pool, and reloads of objects from the dictionary cache to assess if resizing is needed.
2. Use Bind Variables: Using bind variables can significantly reduce parsing overhead and improve scalability. Without bind variables, even slightly different SQL statements are parsed and stored separately, leading to excessive memory consumption and increased parse time.
3. Pin Packages and Procedures: You can use the DBMS_SHARED_POOL package's KEEP procedure to pin frequently used packages, procedures, and triggers in memory. This prevents them from being aged out of the shared pool, which reduces hard parses and improves performance.
4. Avoid Shared Pool Fragmentation: Ensure that your shared pool size and the size of your large pool are set adequately to avoid fragmentation. Monitor the shared pool's free memory and the sizes of the largest chunks of free memory to identify potential fragmentation.
5. Control Session Cursors: Limit the number of open cursors (OPEN_CURSORS parameter) to a realistic number based on your application's requirements. Excessive cursors can lead to increased shared pool usage.
6. Optimize Shared Pool Reserved Size: The shared pool reserved size (SHARED_POOL_RESERVED_SIZE) can be set to manage large allocations. If you have large objects in your shared pool, you may want to increase this size to avoid running out of contiguous space.
7. Cursor Sharing: In some cases, using the CURSOR_SHARING parameter can reduce the amount of memory consumed by duplicate SQL statements. It should be used judiciously as it may impact execution plans.
8. Monitor and Diagnose Issues: Regularly monitoring the shared pool using Oracle's dynamic performance views (V$ views) can help identify potential issues. This includes checking for issues such as excessive invalidations, high reloads, and library cache misses.
9. Automatic Shared Memory Management (ASMM): Enable ASMM so Oracle can automatically adjust the shared pool size based on the workload. The automatic tuning of shared pool sizes can be done by setting the SGA_TARGET parameter.
10. Use Automatic Memory Management (AMM): If you are using Oracle 11g or later, consider using AMM. It allows Oracle to dynamically increase or decrease the size of the shared pool and other SGA components in response to changes in workload.

Remember that tuning should be based on specific needs and evidence from performance metrics, not arbitrary rules. Always test the effects of tuning changes to ensure they have the desired effect.

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.

• Accelerate Executions within cache: 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.

Using session_cached_cursors is especially useful for tasks that repeatedly issue parse calls for the same SQL statement, for instance, where an SQL statement is repeatedly executed with a different variable value. An example would be the following SQL request that performs the same query 50 times, once for each state:
DBMS_LIBCACHE: Prepares the library cache on an Oracle instance by extracting SQL and PL/SQL from a remote instance and compiling this SQL locally without execution

SELECT SUM(sale_amount) 
FROM SALES
WHERE
state_code = :var1;

1. library cache: An area of memory in the shared pool. This cache includes the shared SQL areas, private SQL areas (in a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles.
2. library cache hit: The reuse of SQL statement code found in the library cache.
3. library cache miss: During SQL processing, the act of searching for a usable plan in the library cache and not finding it.

The library cache holds executable forms of SQL cursors, PL/SQL programs, and Java classes. This section focuses on tuning as it relates to cursors, PL/SQL programs, and Java classes. These are collectively referred to as application code. When application code is run, Oracle Database attempts to reuse existing code if it has been executed previously and can be shared. If the parsed representation of the statement does exist in the library cache and it can be shared, then the database reuses the existing code. This is known as a soft parse, or a library cache hit. If Oracle Database cannot use existing code, then the database must build a new executable version of the application code. This is known as a hard parse, or a library cache miss. Search this website for details on when a SQL and PL/SQL Library cache misses can occur on either the parse step or the execute step when processing a SQL statement. When an application makes a parse call for a SQL statement, if the parsed representation of the statement does not exist in the library cache, then Oracle Database parses the statement and stores the parsed form in the shared pool. This is a hard parse. You might be able to reduce library cache misses on parse calls by ensuring that all sharable SQL statements are in the shared pool whenever possible.

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.