Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: 4031 - errors

RE: 4031 - errors

From: Fedock, John (KAM.RHQ) <John.Fedock_at_us.kline.com>
Date: Thu, 27 May 2004 10:21:12 -0400
Message-ID: <5042FDC675779848A113F70D54F58749065A1ABC@KAMRICEXCLUS.us.kline.com>


Can someone elaborate on what exactly is a 'high version count'? If it = is the *same* SQL, then why is not reused?

Running the below statement shows I have statements with version counts = > 200. I do not have curosr_sharing. I have occasional 4031 errors. = My system is 8.1.7.4 on HP-UX.

Thanks for any clarification.

John

select
 sa.version_count vercount,
 ST.sql_text sqltext
from v$sqlarea sa,

     v$sqltext ST
where sa.version_count > 8
and sa.address =3D ST.address
order by 1 DESC,

         ST.piece
;

-----Original Message-----

From: M.K.Jha_at_ponl.com [mailto:M.K.Jha_at_ponl.com] Sent: Thursday, May 27, 2004 7:06 AM
To: oracle-l_at_freelists.org
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

First find the reason for high consumption of shared sql area and then = do the
needful to remove the bottleneck.

=3D=3D=3D=3D=3D=3D=3D=3D=3D

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

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 - 09:19:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US