| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: 4031 - errors
I am not getting this now. I increased the log buffers and shared pool and
so far so good. when I get my head above water I am going to clone prod to
dev and try to reproduce the situation.
-----Original Message-----
From: Danisment Gazi Unal [mailto:dunal_at_ubTools.com]
Sent: Thursday, May 27, 2004 6:13 AM
To: oracle-l_at_freelists.org
Subject: Re: 4031 - errors
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:
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
> 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
> -----------------------------------------------------------------
>
>
>
> --------------------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- This transmission contains information solely for intended recipient and may be privileged, confidential and/or otherwise protect from disclosure. If you are not the intended recipient, please contact the sender and delete all copies of this transmission. This message and/or the materials contained herein are not an offer to sell, or a solicitation of an offer to buy, any securities or other instruments. The information has been obtained or derived from sources believed by us to be reliable, but we do not represent that it is accurate or complete. Any opinions or estimates contained in this information constitute our judgment as of this date and are subject to change without notice. Any information you share with us will be used in the operation of our business, and we do not request and do not want any material, nonpublic information. Absent an express prior written agreement, we are not agreeing to treat any information confidentially and will use any and all information and reserve the right to publish or disclose any information you share with us. ---------------------------------------------------------------- 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 - 08:58:31 CDT
 |
 |