ORA-04031 errors

I recently received ORA-04031 errors on one of our production databases.

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","lock table sys.mon_mods$ in ...","sga heap(1,0)","kglsim object batch")

At first glance it looks like it has run out of memory and we need to increase sga_max_size and sga_max_target. However this is not the cause of the problem and eventually all you'll end up doing is prolonging the time before the error occurs agains as opposed to fixing the root cause.

So how do we diagnose the root cause?

The best place to start is the associated trace file that is created in the database's background_dump_dest directory.

The trace file will contain the Heading

=================================
Begin 4031 Diagnostic Information
=================================

Scroll down to the heading

==============================
Memory Utilization of Subpool 1
================================
Allocation Name Size
_________________________ __________
"free memory " 101905912


This section shows the breakdown of the memory allocation. What we are looking is an allocation that looks unusually large.

In our example there was the following very large allocation

"mvobj part des " 495739432

After more investigation it was revealed that we had hit a bug in Oracle 10.2.0.3 where there is a memory leak with Partitioning DDL. This is documented as fixed in 10.2.0.4. There is an interim patch released to address this.