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:
Shared pool size values
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.
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).
In order to create a reserved list, shared_pool_reserved_size must be greater than shared_pool_reserved_min_alloc.
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.
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.
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
Mouse Over the image shown below to view an example of the output of that query.