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.

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:
  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;

The following 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.
The following section discusses more about Oracle tuning parameters.

Here are the terms to be reviewed.

1. shared_pool_size: Causes shared SQL areas to be de-allocated from the library cache after use
2. cursor_space_for_time: Oracle will cache the cursors for a query
3. session_cached_cursors: Determines the overall size of the shared pool memory
4. dbms_shared_pool: An Oracle-supplied package to pin packages in the shared pool

• SHARED_POOL_SIZE SHARED_POOL_SIZE specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures. If you set PARALLEL_AUTOMATIC_TUNING to false, then Oracle also allocates parallel execution message buffers from the shared pool. Larger values improve performance in multi-user systems. Smaller values use less memory. You can monitor utilization of the shared pool by querying the view V$SGASTAT.
• CURSOR_SPACE_FOR_TIME
  CURSOR_SPACE_FOR_TIME lets you use more space for cursors in order to save time. It affects both the shared SQL area and the client's private SQL area.
  Values:
  1. TRUE: Shared SQL areas are kept pinned in the shared pool. As a result, shared SQL areas are not aged out of the pool as long as an open cursor references them. Because each active cursor's SQL area is present in memory, execution is faster. However, the shared SQL areas never leave memory while they are in use. Therefore, you should set this parameter to TRUE only when the shared pool is large enough to hold all open cursors simultaneously. In addition, a setting of TRUE retains the private SQL area allocated for each cursor between executions instead of discarding it after cursor execution, saving cursor allocation and initialization time.
  2. FALSE: Shared SQL areas can be deallocated from the library cache to make room for new SQL statements.

Property	Description
Parameter type	Integer
Default value	50
Modifiable	ALTER SESSION
Range of values	0 to operating system-dependent
Real Application Clusters	Multiple instances can have different values.

SESSION_CACHED_CURSORS specifies the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed. This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as statements are re-executed by a user.
In the next lesson, you will learn to identify high-use packages for pinning.