[Solution to architecture problems, 1] solution to 4031 errors

III. solution 4031 error method :( ora-4031 error cause: a large number of hard parsing, resulting in chain chunk are changed very small, at this time a large SQL will not find the appropriate chunk, the ora-4031 error will be reported .) If SHARED_POOL_SIZE is set to large enough, and can exclude the factors of the Bug, then most of the ORA-04031 errors

III. solution 4031 error method: (ora-4031 error cause: a large number of hard parsing, resulting in chunk chain are changed very small, at this point a large SQL will not find the appropriate chunk, it will report a ora-4031 error .) If SHARED_POOL_SIZE is set to large enough and the Bug can be ruled out, most ORA-04031 errors

3. Solution to the 4031 error:

(Ora-4031 errors cause: a large number of hard parsing, resulting in chain chunk are changed very small, at this time a large SQL will not find the appropriate chunk, the ora-4031 error will be reported .)

If SHARED_POOL_SIZE is set to large enough and the Bug can be ruled out, most ORA-04031 errors are caused by excessive memory fragments caused by a large number of SQL code in the shared pool.

1. Execute alter system flush shared_pool; clear library cache and rows cache. This method can only temporarily solve the problem and is not recommended.

Cause:

(1) Flush Shared Pool will cause unused cursor to be cleared out of the Shared Pool. If these SQL statements need to be executed subsequently, the database will experience a lot of hard parsing, the system will experience severe CPU contention, and the database will have intense L atch competition.

(2) If the application does not use Bound variables and a large number of SQL statements are not executed, Flush Shared Pool may only provide a short improvement and the database will return to the original state soon.

(3) If the Shared Poo l is large and the system is very busy, refreshing the Shared Pool may cause the system to be suspended. For similar systems, try to do so when the system is idle.

2. Method 2: allow developers to modify SQL statements to share SQL statements,

3. Method 3: save large objects to the memory,

4. Method 4: Set the shared_pool_reserved_size parameter and use the shared_pool_reserved_min_alloc parameter to avoid performance degradation. The default value of this parameter is 5% of shared_pool_size. The recommended value of this parameter is 10% ~ of shared_pool_size ~ The size of 20%. The maximum value cannot exceed 50% of shared_pool_size. The reserved area is used to cache large object areas in the shared_pool, so the size of the reserved area can be increased. # View the number of errors in the request retained area. (If the request retained area is incorrect, the packet will contain an error 4031.) select REQUEST_MISSES from v $ shared_pool_reserved; 6. use AMM Automatic Memory Management to allocate a shared pool to prevent small and incorrect sharing pool settings. (This step is generally done)