RelationalDBDesign RelationalDBDesign


Shared Pool   «Prev 

Shared pool reserved size

Output of the reserved size query
SQL>@shared_pool_reserved

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

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.