Re: 4031 - errors
- From: "Danisment Gazi Unal" <dunal@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 27 May 2004 14:12:48 +0300
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@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> 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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
