Reserved zone and ORA-04031 for Oracle Performance Optimization

1. Loading and aging of large objects in the reserved area without PIN will cause fragments in the sharing pool. Oracle wants to solve this problem by creating a region in the sharing pool,

1. Loading and aging of large objects in the reserved area without PIN will cause fragments in the sharing pool. Oracle wants to solve this problem by creating a region in the sharing pool,

I. Reserved Area

Loading and aging of large objects without PIN will cause fragments in the sharing pool. Oracle wants to solve this problem. It opens up a region in the sharing pool, all objects larger than 4400 bytes will be allocated space in this specially opened area, which is called a reserved area. In this way, separated storage of large and small objects reduces the impact of loading and aging of large objects on a large number of small objects, and reduces memory fragments in the small object area. In addition, Oracle has designed a dedicated memory management algorithm for the reserved area, so that large objects can be loaded more quickly.

You can use the shared_pool_reserved_size parameter to set the size of the reserved area. By default, the reserved area is automatically set to 5% of the size of the Shared Pool. In addition, the size of the reserved area cannot be set to half the size of the Shared Pool. Otherwise, an error is returned. Generally, the size of the reserved area is reserved by default. we generally do not need to adjust it.

Oracle does not recommend you adjust the size of objects in the reserved area. But sometimes 4400 is not suitable for our system. Assume that objects of about 4000 bytes (less than 4400 bytes) are often loaded into the Shared Pool. Objects of 3 and four thousand bytes are already large objects, and these large objects are rarely used, in this way, PIN will be a waste of space in the memory. Less than 4400 of the size makes them unable to be loaded into the reserved area. The loading of these objects at each execution requires aging many small objects, it also easily causes fragments of the Shared Pool. At this time, we can reduce the limit value by 4400. For example, we can reduce the limit of the reserved area to 3500 so that objects that cannot enter the reserved area can enter the reserved area. This not only accelerates the loading of these objects, but also reduces their impact on many small objects. We can use V $ DB _ OBJECT_CACHE to observe the memory occupied by the object in the Shared Pool. If we find the above situation, we can reduce the limit by 4400. This restriction is set with a hidden parameter: _ shared_pool_reserved_min_alloc. All parameters with underscores before the name are called hidden parameters. These hidden parameters cannot be displayed through Show parameter. We can use the script Show_para. SQL to Show hidden parameters and their meanings, for how to write this script, see the appendix at the end of this chapter.

There is a view that can help adjust the reserved area: V $ SHARED_POOL_RESERVED, which has the following columns:

FREE_SPACE: total free space in the reserved area

AVG_FREE_SIZE: Average size of each free memory block

FREE_COUNT: number of free memory blocks

MAX_FREE_SIZE: Maximum free memory block size

USED_SPACE: the space used in the reserved area

AVG_USED_SIZE: Average size of each used memory block

USED_COUNT: Number of memory blocks in use

MAX_USED_SIZE: The maximum memory block size used

REQUESTS: Number of times free memory blocks are searched in the reserved area

REQUEST_MISSES: the number of times that no memory block can meet the requirements in the reserved area and the LRU chain starts to refresh the object in the reserved area

LAST_MISS_SIZE: size of the requested space when REQUEST_MISSES occurs last time

MAX_MISS_SIZE: maximum size of the requested space when REQUEST_MISSES occurs

The following columns are valid even if SHARED_POOL_RESERVED_SIZE is not set:

REQUEST_FAILURES)

LAST_FAILURE_SIZE: size of the memory requested during the last REQUEST_FAILURES request

The last three columns are related to the DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD process.

ABORTED_REQUEST_THRESHOLD: the minimum size exceeds the value set by DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD.

ABORTED_REQUESTS: the number of times that DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD is exceeded

LAST_ABORTED_SIZE: The last time the size exceeds the value of DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD.

NOTE: If REQUEST_FAILURES is greater than 0 and continues to increase, it indicates that the size of the memory block is N, but the size of the shared pool does not exceed N. When REQUEST_FAILURES increases faster, the user's operation will fail and receive the famous ORA-04031 error, that is, "The Shared Pool memory is insufficient ", there is usually another saying "You cannot apply for memory blocks larger than N Bytes ".

There are three reasons for this. First, the memory is insufficient. In this case, you need to increase the memory. Second, there is still memory in the shared pool, but they are all small blocks. There is no memory block larger than N Bytes to meet user requests. That is to say, there is fragmentation in the memory. Let's talk about the solution to memory fragmentation in the shared pool right away. Let's look at the third case. Third, there is still memory in the shared pool. But because the reserved area settings are unreasonable, the user must request the memory in the normal area (the size of the memory requested by the user does not exceed 4400 ), the normal zone has no free memory, but there is still a lot of free memory in the reserved zone. Or, on the contrary, the user requests the memory in the reserved area. The reserved area memory is insufficient, but there is still a large amount of memory available in the normal area.

When a 4031 error occurs, whether your situation is in the third category-the reserved settings are unreasonable. You can find out through the view described in this section. The following describes how to monitor and solve the problems of insufficient space in common areas and insufficient space in reserved areas:

(1) Insufficient memory space in common areas:

When REQUEST_FAILURES is not 0 and continues to increase, if LAST_FAILURE_SIZE <_ limit (minimum size limit of objects entering the reserved area), for example, _ limit is still the default value of 4400, while LAST_FAILURE_SIZE is 3800, that is, the memory size requested by the user is 3800 bytes when the last user request memory fails. 3800 is less than 4400, which indicates that the user failed to request memory in the normal zone. Check whether there is sufficient memory in the reserved area. If there is sufficient memory, you can set _ SHARED_POOL_RESERVED_MIN_ALLOC to be smaller than 3800 so that your requests can be allocated within the reserved area, or simply reduce the size of the reserved area, in this way, the space in the common area will naturally increase. So, if you are sure that the memory in the reserved area is sufficient? You can observe the following columns:

FREE_SPACE: total free space in the reserved area

AVG_FREE_SIZE: Average size of each free memory block

FREE_COUNT: number of free memory blocks

MAX_FREE_SIZE: Maximum free memory block size

USED_SPACE: the space used in the reserved area

AVG_USED_SIZE: Average size of each used memory block

USED_COUNT: Number of memory blocks in use

MAX_USED_SIZE: The maximum memory block size used

If FREE_SPACE shows a lot of free space, it means there is still a lot of space in the reserved area. You can set _ SHARED_POOL_RESERVED_MIN_ALLOC to a smaller value or reduce the memory occupied by the reserved area.

If the reserved area does not have enough space, you need to check whether there are too many fragments in the Shared Pool. If not, you only need to increase the size of the Shared Pool. We will discuss the fragmentation right away.

(2) Insufficient reserved area space

When REQUEST_FAILURES is not 0 and continues to increase, LAST_FAILURE_SIZE> _ SHARED_POOL_RESERVED_MIN_ALLOC indicates that the memory requested by the user should be allocated in the reserved area, in this case, the reserved area does not have enough space to meet the user's request.

In this case, we can increase the limit value of _ SHARED_POOL_RESERVED_MIN_ALLOC to reduce the number of objects entering the reserved area. Set the value of SHARED_POOL_RESERVED_SIZE to a greater value to increase the size of the reserved area. Of course, if you still have free memory on your host, you can increase the memory size of the Shared Pool.