Setting and function of shared_pool_reserved_size parameters-zhuanzai

There is one more parameter that needs to be mentioned:shared_pool_reserved_size. This parameter specifies the reserved shared pool space that is used to satisfy future large contiguous shared pool space requests. When there is too much fragmentation in the shared pool, requesting large chunks of space can result in Oracle's large-scale lookup and freeing of shared pool memory to satisfy the request, which can lead to more severe performance degradation, setting the appropriate shared_pool_reserved_size parameters, and combining _shared_pool The _reserved_min_alloc parameter can be used to avoid the resulting performance degradation.

The ideal value for this parameter should be large enough to satisfy any memory request to the reserved list, without requiring the database to refresh the object from the shared pool. The default value of this parameter is Shared_pool_size 5%, usually the recommended value for this parameter is the 10%~20% size of the shared_pool_size parameter, and the maximum must not exceed Shared_pool_size 50%.

Similarly, in the trace file, you can find memory information about the reserved list (RESERVED list):

RESERVED Free LISTS:
Reserved Bucket 0 size=16
Reserved Bucket 1 size=4400
Reserved Bucket 2 size=8204
Reserved Bucket 3 size=8460
Reserved Bucket 4 size=8464
Reserved Bucket 5 size=8468
Reserved Bucket 6 size=8472
Reserved Bucket 7 size=9296
Reserved Bucket 8 size=9300
Reserved Bucket 9 size=12320
Reserved Bucket Ten size=12324
Reserved Bucket size=16396
Reserved Bucket size=32780
Reserved Bucket size=65548
Chunk 41000050 sz= 212888 r-free ""
Chunk 41400050 sz= 212888 r-free ""
Chunk 41800050 sz= 212888 r-free ""
Chunk 41c00050 sz= 212888 r-free ""
Chunk 42000050 sz= 212888 r-free ""
Chunk 42400050 sz= 212888 r-free ""
Chunk 42800050 sz= 212888 r-free ""
Chunk 42c00050 sz= 212888 r-free ""
Chunk 43000050 sz= 212888 r-free ""
Chunk 43400050 sz= 212888 r-free ""
Chunk 43800050 sz= 212888 r-free ""
Chunk 44000050 sz= 212888 r-free ""
Total reserved free space = 2554656

_shared_pool_reserved_min_alloc The value of this parameter controls the use and allocation of reserved memory. If a large chunk of memory request is not available in the shared pool free list, the memory allocates a larger amount of space from the reserved list (RESERVED list).

In different versions, the default value for this parameter is always 4400, and the following output is from the Oracle 11GR1 version:

[Email protected]> @GetHidPar
Enter value for Par:shared_pool_reserved_min_alloc
Old 4:and x.ksppinm like '%&par% '
New 4:and x.ksppinm like '%shared_pool_reserved_min_alloc% '
NAME VALUE Describ
----------------------------------- ---------- ------------------------------------------------------------------ ----
_shared_pool_reserved_min_alloc 4400 minimum allocation size in bytes for reserved area of shared pool

The default value for this parameter is sufficient for most systems. If the system often ORA-04031 errors are requests greater than 4400 memory blocks, then you may need to increase the shared_pool_reserved_size parameter settings.

And if the main cause of LRU merging, aging and ORA-04031 errors in memory requests between 4100~4400bytes, then reduce the _shared_pool_reserved_min_alloc and appropriately increase Shared_pool_ Reserved_size parameter values are usually helpful. Setting _shared_pool_reserved_min_alloc=4100 can increase the probability that a shared pool will successfully satisfy the request. It should be noted that the modification of this parameter should be combined with the modification of the shared pool size and the shared pool Reserved size. Setting _shared_pool_reserved_min_alloc=4100 is a proven and reliable way, and it is not recommended to set lower.

Query v$shared_pool_reserved view can be used to determine the cause of a shared pool problem, the following query comes from a business system, note that the system has failed 2 requests, the last requested memory block size is 3896 Bytes. Because this environment does not report ORA-04031 errors, the _shared_pool_reserved_min_alloc parameter is not modified.

[Email protected]> Select free_space,avg_free_size,used_space,avg_used_size,request_failures,last_failure_size
2 from V$shared_pool_reserved;

Free_space avg_free_size used_space avg_used_size request_failures last_failure_size
---------- ------------- ---------- ------------- ---------------- -----------------
44406648 727977.836 86640 1420.32787 2 3896

If Request_failures > 0 and last_failure_size > _shared_pool_reserved_min_alloc, Then the ORA-04031 error may be caused by the lack of contiguous space in the shared pool reserved space. To solve this problem, consider increasing the _shared_pool_reserved_min_alloc to reduce the number of objects that buffer into the shared pool reserve space and increase the shared_pool_reserved_size and Shared_pool_ Size to increase the available memory for the shared pool reserved space.

If Request_failures > 0 and Last_failure_size < _shared_pool_reserved_min_alloc, then the lack of contiguous space in the library cache causes a ORA-04031 error.

Setting and function of shared_pool_reserved_size parameters-zhuanzai