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.
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:
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:
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
Mouse Over the image shown below to view an example of the output of that query.
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