RelationalDBDesign RelationalDBDesign


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.

Sizes

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. 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

Shared Pool Reserved Size
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