ORA-04031 is one of the most common errors we used to get in our DBA life. Today we will discuss how to resolve this. Let’s go through the steps in Detail :
So, when Oracle tries to allocate memory from the Shared Pool (or another SGA component) but fails due to memory fragmentation or a lack of free space, we get this ORA-04031 error.
Table of Contents
Error :
ORA-04031: unable to allocate XXXX bytes of shared memory ("<pool>","<object>","<component>","<allocation>").Cause :
It can happen due to many reasons, as follows :
- Insufficient memory: Shared pool size is too small
- Shared Pool Fragmentation: Too many hard parses due to unshared SQL.
- High Parse Rates: Sometimes, in the query, Application Teams are not using bind variables; they use literals. This causes too many hard parse and lack of shared pool
- Cursor Leaks: The Application Team opens many cursors but does not close them
Resolution :
1. Flush Shared Pool Temporarily :
ALTER SYSTEM FLUSH SHARED_POOL;
Be cautious while issuing this command; this may clear cached SQLs, which can cause a CPU spike.
2. Increase Shared Pool Size :
If the shared pool size is too small, then we need to increase the shared pool by issuing the below command :
ALTER SYSTEM SET shared_pool_size = 10G SCOPE=SPFILE;
So it needs a DB restart, as this is a static parameter.
3. Using Bind Varibles :
We can ask the Application Team to use bind variables that are accessed frequently. It will reduce hard parsing as well.
-- Bad (causes hard parse)
SELECT * FROM employees WHERE emp_id = 203;
-- Good (uses bind variable)
SELECT * FROM employees WHERE emp_id = :emp_id;
4. Enable Automatic Memory Management (AMM) if required
This is applicable for 11g or later versions.
ALTER SYSTEM SET MEMORY_TARGET=2G SCOPE=SPFILE;
Hope this helps!! Also, you can check from the Official site as well below :
Oracle Support Doc: ORA-04031 Troubleshooting Guide (Doc ID 146599.1)
Other Oracle Related Articles :