ORA-04031: unable to allocate n bytes of shared memory


ORA-04031: unable to allocate n bytes of shared memory
Cause: More shared memory is needed than was allocated in the shared pool.
Action: If the shared pool is out of memory, either use the DBMS_SHARED_POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE.

Reference: Oracle Documentation

I have seen a alot of Oracle DBA to bluntly say, if you encounter ORA-04031 then Increase SHARED_POOL_SIZE parameter and restart the database. With due respect to them, Yes it is one of the solution of ORA-04031, but should not we first identify the issue and then suggest an action. ORA-04031 could be due to another reasons and not simply because the shared pool is too small.

Understand ORA-04031:
When a process tries to allocate contiguous memory in the shared pool and fails to find memory required, then it would lead to ORA-04031. There could be many reasons for ORA-04031, some of the important are following
- Inadequate Sizing of Shared Pool
- Not using bind variables
- Not limiting number of rows in BULK COLLECT
- Too many pinned packages
- Objects Candidate for Pinning but not Pinned

Unfortunately, Most of the application developers do not care about using Database efficiently and abuse shared pool by not using bind variables and as a result generating a unique SQL statement for every single database call made. Extra memory shared pool may supress ORA-04031 for some time, eventually the problem will reoccur if you don't deal with the source of the problem.

In this case you will need to look at temporary workarounds until the SQL abusing applications are fixed, you can use one of the following:
- forcing cursor sharing by "alter system set cursor_sharing='force' scope=both" and bounce instance.
- flushing shared pool by doing "alter system flush shared pool"
- Heavy fragmentation of shared pool can also be fixed by bouncing the instance.

I have also seen ORA-04031 due to not using LIMIT clause in BULK COLLECT in case when Automatic Memory Management was configured. ORA-04031 also rises when too many objects are pinned in Shared Pool and space is not adequately left for running processes.

Tips to avoid ORA-04031:
- Always use Bind Variables.
- Use LIMIT clause with BULK COLLECT
- Pin required and most used objects in Shared Pool
- Reduce un-necessary use of Shared Pool
- Use Automatic Memory Management or Automatic SGA Management

Yes, the ultimate solution is to increase shared_pool_size and/or shared_pool_reserved_size or SGA_TARGET/MEMORY_TARGET.

I hope this was an useful information to you, please provide your feedback.


Related Links
- ORA-27101: shared memory realm does not exist
- ORA-00600 internal error code
- ORA-01034: ORACLE not available
- ORA-00849: SGA_TARGET cannot be set to more than MEMORY_MAX_TARGET
- ORA-00018 maximum number of sessions exceeded
- ORA-00020 maximum number of processes exceeded

8 comments:

  1. Check the MAX_SIZE and MIN_SIZE of individual SGA components from v$sga_dynamic_component. Based on the findings, resize the individual memory components to a minimum threshold value,(sum them up + 25% of their total size) to estimate the sga_target for your system to enable ASMM.

    ReplyDelete
  2. cursor sharing force, well this is a bad practice, because developers cannot write efficient code.

    ReplyDelete
  3. Rally nice and condensed article giving a bit more light into possible issues behind the error.
    Thanks for sharing.


    Jacek Gebal
    Senior Oracle Developer at Fidelity

    ReplyDelete
  4. Nazzareno Maria RezziniMay 20, 2015 at 9:48 AM

    You can take a look at the document in MOS:
    Diagnosing and Resolving Error ORA-04031 on the Shared Pool or Other Memory Pools [Video] (Doc ID 146599.1).
    Here you can review any way for each RDBMS release to narrow down the potential causes and sort out the error.

    ReplyDelete
  5. General assumption when this error come is to increase SGA for that you need to really check if something has changed from application side that it needs more SGA.

    and another issue could be with version , if you are in 11.2.0.2 or lesser then there is a bug where shared pool size drastically increase due allocation of bytes to KGLH0 (sub-pool).

    It can be fixed by upgrading to 11.2.0.4

    ReplyDelete
  6. Re-starting the database will not resolve the problem. Here's another way to debug the error. Login to the oracle database as admin (I do this a lot in the oracle enterprise manager) and run the program. Check the SQL generated on the background. From there you can analyze what's going on. Then copy and paste that SQL statement and run it manually on your TOAD or PL/SQL and see what's going on.

    ReplyDelete
  7. Agree, sql needs to be checked first, and force cursor sharing is too aggressive, it will turn every literal it can see into bind variables.

    The data only tends to grow, and tables that worked well for ages will suddenly need partitioning, joins will need re-organising.

    Splitting the queries using temporary tables might be a solution.

    It really depends on particular circumstances.



    Olga Sermon
    Data Analyst at Vanderlande

    ReplyDelete