4031 - errors 2004-05-27 - By Danisment Gazi Unal
Hi,
Without HEAPDUMP, It may be hard to diagnose ORA-4031 (See ORA-4031.ora-code.com). 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@(protected) >
To: <oracle-l@(protected) >
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 (See ORA-04031.ora-code.com).
> 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@(protected) >
> To: <oracle-l@(protected) >
> 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@(protected)
> 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@(protected)
> 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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
