| 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
Good advise from both of you and I will look at that. I know how to look at
the cursors to tell them.
But is there a good way to tell if I am running into the db link or
functional index leak. I can see what shared pool I grab and buffers from
x$bh
but I haven't found a good way to see if the memory is leaking.... how would
I do this? Oracle hasn't be very supportive, and I look all over Metalink
and most of the time they just say increase this and that and bounce.
Well designed - not here. Cursor sharing is exact, we do use alot of in and between. we are cost base and keep up to date with stats.
I need to look at the .net stuff as well since that seems to be a major consumer of our resources when it runs.
Thanks for all the idears.
K
-----Original Message-----
From: M.K.Jha_at_ponl.com [mailto:M.K.Jha_at_ponl.com]
Sent: Thursday, May 27, 2004 6: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.
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/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.
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
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.
 |
 |