//
you're reading...
Database

Solving ORA-04031 Error Examples

Oracle error code 4031 states: unable to allocate %s bytes of shared memory (\”%s\”,\”%s\”,\”%s\”,\”%s\”). Generally this error happens when more shared memory is needed than was allocated in the shared pool or Streams pool. When a database has Automatic Shared Memory Management (ASMM) turned on, the DBA should look at the current SGA dynamic components and SGA resize operations first before trying to give more memory to SGA/shared pool.

Oracle support now offers an automatic diagnosing tool for ORA-04031 error without requiring customers to open a SR. DBA just needs to upload the alert log file and the trace file generated by ORA-04031. The tool will analyze the files and then provides its findings and suggestions.

One of our databases started to have ORA-04031 errors. I uploaded the alert log file and the first trace file for the error to My Oracle Support. It came back with following findings/suggestions:

=============================================================================

Our analysis shows the Shared Pool “free memory” appears to be available but there are problems getting to large enough chunks of memory to satisfy memory requests. We analyzed common factors leading to “fragmented” memory chunks but did not find a clear root cause for the ORA-4031.
Evidence Details:
** In your trace file, HEAP DUMP heap name = sga heap(1,0)

ORA-04031: unable to allocate 4064 bytes of shared memory (“shared pool”,”unknown object”,”sga heap(1,0)”,”kglsim heap”)

============================================================================

The diagnosing tool told us that we had free memory for shared pool. It was just that the memory was fragmented so the database couldn’t get big enough chunk of memory to shared pool. View v$sga_resize_ops showed many operation failures when shared pool tried to grow merely by 26 MB.

COMPONENT  OPER_TYPE OPER_MODE STATUS Initial(MB) Target(MB) Final(MB) START_TIME           END_TIME

shared pool  GROW  IMMEDIATE  ERROR  1,312    1,328    1,312      JUL-10-2012 09:23:36 JUL-10-2012 09:23:36

From Oracle Support, memory fragmentation issues can be difficult to diagnose and often fall into the application tuning arena. To quickly fix the problem I looked at the other dynamic components of SGA and found large_pool_size was set to 512 MB. Large pool is used primarily for parallel queries and RMAN. This is an OLTP database. There are not much parallel queries from the application. Checking V$SGA_RESIZE_OPS view again did not find any operations of large pool resize. Therefore it is safe to lower the fixed value of 512 MB for large pool to 200 MB. After this change, the shared pool can grow and error did not happen again.

COMPONENT  OPER_TYPE OPER_MODE STATUS Initial(MB) Target(MB) Final(MB) START_TIME     END_TIME

shared pool  GROW IMMEDIATE COMPLETE 1,312 1,328 1,328 JUL-10-2012 10:32:34 JUL-10-2012 10:32:34

shared pool  GROW DEFERRED  COMPLETE 1,328 1,392 1,392 JUL-10-2012 10:47:45 JUL-10-2012 10:47:47

Advertisements

About Hong Wang

I am an Oracle DBA, working in Oracle database since version 7.3. Worked in both application development and production support. Many experiences in real world complicate problems and database projects. This blog serves as a collections of notes I write on my database studies as well as issues I encountered/solved. Your comments are well welcome.

Discussion

3 thoughts on “Solving ORA-04031 Error Examples

  1. Hi
    Thankyou for the post. can i get the name of the diagonistic tool and where can i locate it.

    Regards
    DBA

    Posted by Geeta M | March 21, 2014, 10:55 pm
  2. Hi,
    Thanks for your post.

    But I have the question. How do you know to set size: “large_pool_size was set to 512 MB”.
    IF you design file batch(script have excuse query to oracle) and config it run auto in server every 10 minutes.

    So, I think that, IF you run that file long times it’s will exceed 512MB and it will throw error ORA-04031(I think).
    Do you know the solution to fragmentation memory of oracle.

    Thanks and Best regards,
    Vuong Nguyen

    Posted by Vuong | April 9, 2015, 11:58 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: