Re: 4031 - errors

From: Danisment Gazi Unal <dunal_at_ubTools.com>
Date: Thu, 27 May 2004 14:12:48 +0300
Message-ID: <021a01c443db$8f0d9f70$fe19100a@danismentgu>

Hi,

Without HEAPDUMP, It may be hard to diagnose ORA-4031. If you did not set HEAPDUMP trace for this error, set it as below:

event="4031 trace name HEAPDUMP level 2"

After this error occurs, a trace file will be generated. Then, paste your trace.

danisment...

best regards...

http://www.ubTools.com
Web Based Oracle Products and Services

Original Message ----- From: <M.K.Jha_at_ponl.com> To: <oracle-l_at_freelists.org> Sent: Thursday, May 27, 2004 2:06 PM Subject: Re: 4031 - errors

> Hi Lists,
>
> Jonathan is right.
> There may be two possible reasons for this error:
> 1) really have very low value of allocated for shared sql area or
> 2) High version counts of cursors are consuming lots of sharable memory.
>
> In well design production system, reasons two are best candidate for
ORA-04031.
> I have encountered the same problem in our prod. systems where cursor
sharing
> are restricted for below mentioned case:
> --cursor text have " IN / BETWEEN" in where clause
> -- Cursor_Sharing ="FORCE"
> -- optimizer is cost base
>
> First find the reason for high consumption of shared sql area and then do
the
> needful to remove the bottleneck.
>
>
>
>
> =========
>
> It is quite possible that the 'cursor_space_for_time'
> setting is relevant. When set to true, this pins cursor
> run-time memory into the library cache (faking the
> situation of every cursor in the cache being open
> and active. Since this memory can no longer be
> freed, you can easily run out of memory if you
> have made a small error in your estimate of
> number of sessions and number of different
> sql statements.
>
> Bringing a new module into the system, adding
> a new user to the system, allowing someone to
> run ad hoc SQL, adding a monitoring tool -
> anything which increases the number of SQL
> statements, or number of sessions executing
> shared sql statements, could take you into 4031.
>
> On the other hand, is could just be a bug where
> Oracle is generating lots of copies of cursors
> that should be shared but aren't. I've just come
> back from a site where there were several hundred
> copies of some cursors visible in v$sql - after only
> a handful of invalidations or reloads - and only a few
> dozen active sessions.
>
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Optimising Oracle Seminar - schedule updated May 1st
>
>
> ----- Original Message -----
> From: "Duret, Kathy" <kduret_at_starkinvestments.com>
> To: <oracle-l_at_freelists.org>
> Sent: Wednesday, May 26, 2004 8:53 PM
> Subject: 4031 - errors
>
>
> Had a strange problems on Friday afternoon. We are on 8.1.7.4 Solaris
>
> Had a ton of Bam - ora_04031 errors.
>
> Tried to flush the shared pool and it would release some space but a large
> chuck was not being released and this was strange since we have only
acouple
> of objects pinned.
>
> When I tried to pin an object that was failing right after I would flush
the
> shared_pool I couldn't get space.
>
> Nothing new was put in that week as far as I know.
>
> Put in a tar with Oracle and they suggested to bounce the database - which
I
> did later, increased the shared_pool and session_cached_cursors
> We have pinned some more objects that are being loaded alot.
>
> I looked at the sql area and nothing looked horrible, all the same normal
> stuff. In fact there was really no load on the database or machine, no
> swapping or paging.
>
> It was very strange. It looked like "something" had memory and wasn't
> releasing it.
>
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>
>
> --------------------------------------------------------------------------

> The contents of this e-mail are confidential to the ordinary user of the
> e-mail address to which it was addressed and may also be privileged. If
you
> are not the addressee of this e-mail you should not copy, forward,
disclose or
> otherwise use it or any part of it in any form whatsoever. If you have
> received this e-mail in error please notify us by telephone or e-mail the
> sender by replying to this message, and then delete the e-mail and other
> copies of it from your computer system. Thank you.
>
> We believe this email to be virus free but do not warrant that this is the
> case and we will not accept liability for any losses arising from any
virus
> being transmitted unintentionally by us.
>
> We reserve the right to monitor all E-mail communications through our
network
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu May 27 2004 - 06:09:36 CDT