Shared Pool   «Prev  Next»
Lesson 4 Tune the shared pool reserved size
Objective Reserve Space for Large Objects with shared_pool_reserved_size.

Reserve Space for Large Objects with shared_pool_reserved_size

In addition to the shared_pool_size parameter, Oracle provides a special parameter called shared_pool_reserved_size that serves to reserve space for very large objects. The reserved pool space prevents large packages or PL/SQL from fragmenting in the shared pool and ensures that they get contiguous memory space.

Shared Pool Disruptions

On busy systems, Oracle may have difficulty finding a contiguous piece of memory to satisfy a large request for memory. Because Oracle will search for and free currently unused memory, the search for this large piece of memory may disrupt the behavior of the shared pool, leading to more fragmentation and poor performance.
Smaller objects will not fragment the reserved list, helping to ensure the reserved list will have large contiguous chunks of memory. Once the memory allocated from the reserved list is freed, it returns to the reserved list.
The size of the reserved list, as well as the minimum size of the objects that can be allocated from the reserved list, is controlled via init.ora parameters:
  1. shared_pool_reserved_size and
  2. shared_pool_reserved_min_alloc.

Shared pool size values

  1. If not specified in your init.ora file, the shared_pool_reserved_size will default to a value of five percent of the value of shared_pool_size.
  2. The range of values for shared_pool_reserved_size can go from shared_pool_reserved_min_alloc to one half of shared_pool_size (in bytes).
  3. In order to create a reserved list, shared_pool_reserved_size must be greater than shared_pool_reserved_min_alloc.
  4. Ideally, the shared_pool_reserved_size parameter should be large enough to satisfy any request scanning for memory on the reserved list without flushing objects from the shared pool.
  5. In general, you should set shared_pool_reserved_size to 10 percent of shared_pool_size. For most systems, this value will be sufficient if you have already tuned the shared pool.

dbms_shared_pool package member Sizes

Question: What does the dbms_shared_pool package member called "sizes" display? The DBMS_SHARED_POOL.SIZES procedure, a part of the DBMS_SHARED_POOL package in Oracle, is a built-in package procedure designed to help analyze and manage the shared pool portion of the System Global Area (SGA).
The DBMS_SHARED_POOL.SIZES procedure provides information about the distribution of the chunks (pieces of memory) within the shared pool. This information can be useful for understanding the allocation and fragmentation of memory within the shared pool.
Specifically, the DBMS_SHARED_POOL.SIZES procedure displays the number of chunks in the shared pool that are of a certain size or larger. For example, when invoked with a parameter of 1000, it will display the count of chunks in the shared pool that are 1000 bytes or larger.
Here is an example of how to use it:
EXECUTE DBMS_SHARED_POOL.SIZES(1000);

This procedure does not return its results as a query would, but instead prints them to the session's standard output (usually this will be your SQL*Plus session). The output might look something like this:
Sizes                   Count
----------------- ----------
                   2048   191
                   4096   114
                   8192    39
                  16384    15
                  32768     8
                  65536     2
                 131072     1

In this example output, there are 191 chunks of 2048 bytes or larger, 114 chunks of 4096 bytes or larger, and so on.
The DBMS_SHARED_POOL.SIZES procedure can be a valuable tool for diagnosing issues related to shared pool memory usage, and is especially useful in instances where you suspect that shared pool fragmentation may be contributing to performance issues. However, it should be noted that this procedure only provides raw counts of chunk sizes, and may require further analysis or context to inform any tuning strategies or decisions.
The dbms_shared_pool package member called "sizes" will display all SQL and PL/SQL that is in the library cache that is greater than the specified size. For example, to see all SQL greater than 10K, enter: Query the V$SHARED_POOL_RESERVED view.
Oracle provides the V$SHARED_POOL_RESERVED view to provide information on the behavior of the reserved size. Here is a query that will display these values.

column  free_space format 999,999
column  avg_free   format 99,999
column  used_space format 99,999
column  avg_used   format 99,999
column  requests   format 99,999
column  miss       format 99,999
column  fail       format 99,999
select
   free_space,
   avg_free_size avg_free,
   used_space,
   avg_used_size avg_used,
   requests,
   request_misses miss,
   request_failures fail
from
  V$SHARED_POOL_RESERVED;

Mouse Over the image shown below to view an example of the output of that query.

1) FREE_SPACE 2) AVG_FREE 3) USED_SPACE 4) AVG_USED 5) REQUESTS 6) MISS 7) FAIL
  1. This is the total amount of free space in the shared pool.
  2. This is the average size of a free space chunk in the shared pool.
  3. This is the total amount of used space in the shared pool.
  4. This is the average size of a used space chunk in the shared pool.
  5. This is the number of requests. Internally, it is the number of times that the pool list was searched for a free piece of memory.
  6. This is the number of times the pool list didn't have a free piece of memory to satisfy the request, and proceeded to start flushing objects from the LRU list.
  7. This is the number of times that no memory was found to satisfy a request (e.g., number of times ORA-4031 occurred).
1) FREE_SPACE 2) AVG_FREE 3) USED_SPACE 4) AVG_USED 5) REQUESTS 6) MISS 7) FAIL

Using SHARED_POOL_RESERVED_SIZE

The default value for SHARED_POOL_RESERVED_SIZE is 5% of the SHARED_POOL_SIZE. This means that, by default, the reserved list is configured. If you set SHARED_POOL_RESERVED_SIZE to more than half of SHARED_POOL_SIZE, then Oracle Database signals an error. Oracle Database does not let you reserve too much memory for the reserved pool. The amount of operating system memory, however, might constrain the size of the shared pool. In general, set SHARED_POOL_RESERVED_SIZE to 10% of SHARED_POOL_SIZE. For most systems, this value is sufficient if you have tuned the shared pool. If you increase this value, then the database takes memory from the shared pool. (This reduces the amount of unreserved shared pool memory available for smaller allocations.) Statistics from the V$SHARED_POOL_RESERVED view help you tune these parameters. On a system with ample free memory to increase the size of the SGA, the goal is to have the value of REQUEST_MISSES equal zero. If the system is constrained for operating system memory, then the goal is to not have REQUEST_FAILURES or at least prevent this value from increasing. If you cannot achieve these target values, then increase the value for SHARED_POOL_RESERVED_SIZE. Also, increase the value for SHARED_POOL_SIZE by the same amount, because the reserved list is taken from the shared pool.

In the next lesson, you will learn to identify high-use packages for pinning.

Oracle Tuning Parameters

Before moving on to the next lesson, click the link below to learn about Oracle Tuning Parameters.
Oracle Tuning Parameters