Re: Error ORA-04031

From: Bass Chorng <bchorng_at_yahoo.com>
Date: 13 Mar 2002 12:50:34 -0800
Message-ID: <bd9a9a76.0203131250.5b39a9b3@posting.google.com>

"Stephen Ashmore" <sashmore_at_neonramp.com> wrote in message news:<u8ueflkuc0161b_at_corp.supernews.com>...
> This is a common problem caused by fragmentation of your shared pool.
> For a short term solution issue
> alter system flush shared pool;
>

In most cases of 4031, flushing does not work. The theory is simple, if you can flush, so can Oracle age. It does not need to wait until you to do it.

Most likely, your shared pool is small or you are not pining large objects. Also check your cursor_space_for_time. You should not turn it on if you are not fully sharing your SQLs. If you are not sure, turn it off. Once it is on, Oracle can not age a SQL out if there are open cursors referencing to it. If you want it on for better performance, then increase shared pool size.

>
> There is also a bug in early 8.1.7 versions where there is a meomory leak.
> Upgrading to 8.1.7.2 will resolve it.
>

The bug is 1397603. There is a memory leak in the shared pool. If you collect the statistics over time on Shared Pool State Objects from v$sgastat, you will see that it continue to grow until there is nothing left for SQL area. Received on Wed Mar 13 2002 - 14:50:34 CST