|
ORA-4031 is a very common error that many dba's face in their day to day activities. This error can commonly occur due to the SHARED POOL SIZE. This error can be due to an inadquate sizing of the SHARED POOL or due to fragmentation of the shared pool.
Oracle Shared Pool Memory Inadequate Sizing: The first thing is determining if the ORA-04031 error is a result of lack of contiguous space in the library cache. This can be veriried by validating the following from V$SHARED_POOL_RESERVED
1. REQUEST_FAILURES >0 and LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC
2. REQUEST_FAILURES=0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC
If the above cases are true, consider allowing the database to put more objects in shared pool reserved space by lowering SHARED_POOL_RESERVED_MIN_ALLOC. If the problem is still not resolved, consider increasing the SHARED_POOL_SIZE
Oracle Shared Pool Memory Fragmentation: The issue can be attributed to fragmentation if REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is > SHARED_POOL_RESERVED_MIN_ALLOC.
To resolve this issue, you should increase the SHARED_POOL_RESERVED_MIN_ALLOC to reduce the number of objects being cached into the shared pool reserved space. Also increase the SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE to incrase the available memory in the shared pool reserved space. It is recommended that the reserved space be sized to be 10% of the total shared pool area.
There are also a couple of bugs in Oracle database that have been known to cause this error when using multiple Pools. Starting in 10g, the usage of Automatic Memory Management greatly reduces the tuning requirement in this area for many of the basic flavors of ORA-4031.
Oracle Database Support Portal Metalink contains a number of Technical Documents and Notes related to this topic. Refer to Note : 146599.1
|
