Anita,
Thanks, but there is something else going on. I set the event again today,
and a user got the 4031 error (actually a developer). There was no SQL in
the trace file, and it showed plenty of free space, but quite fragmented
(FREE LISTS section). I am going to increase the shared_pool_size
temporarily to alleviate this, but I don't think that's the final solution
(pinhit ratio is OK but not great). The problem is probably not from Bug
1640583 as one of the other DBAs applied this patch (anyway, Metalink
implies that this is solely for AND-EQUALS).
I have noticed that almost all of my entries in the trace files are from the
library cache heap(?). In the past when I traced for a 4031, it was due to
massive SQL (~16 pages) generated by an application filling the shared pool.
In that case, almost the entire pool was in the sql area heap. I
understand that, but what exactly is in the library cache heap? execution
plans? What would lead to most entries in the shared pool being library
cache? If it was bad SQL (not shared), I would expect a bunch of sql area
entries also. That is why I am afraid that just increasing the
shared_pool_size will just postpone the inevitable.
Henry
-Original Message-
Sent: Sunday, September 02, 2001 12:31 PM
To: Multiple recipients of list ORACLE-L
Henry,
The event will cause a trace file to be generated for
any session that encounters an ORA-4031. Is it
possible that it wasn't one of the sessions being
logged that generated the error? If you look in the
trace file you should see the current SQL being
executed that may give you more info about the session
generating the error.
Also, be aware that there's a bug on 8.1.6.3 where
ORA-4031's can occur attempting to allocate state
objects, often accompanied by heavy latch contention
on the 'cache buffer chains' latch. This is bug
1640583. You might want to check with support as
there are PSE's (patchset exceptions) available for
several platforms.
This is not the same as bug 1397603 where ORA-4031's
are also reported when allocating state objects. This
bug is specific to 8.1.7 and the workaround is to set
_db_handles_cached = 0.
HTH,
-- Anita
--- Henry Poras [EMAIL PROTECTED] wrote:
We have been having occasional ORA-4031 problems. I
will probably need to
increase the shared_pool, but before doing that I
wanted to get some extra
information. Yesterday I entered ALTER SYSTEM SET
EVENTS '4031 trace name
heapdump level 2'. This morning I found a few trace
files in the udump
directory. They all seemed to show enough free space
(I looked at the size
of the free space chunks in the FREE LIST section)
so I didn't understand
why we would have the 4031. I asked one of our
developers to send me the log
file of the application where the 4031 shows up, and
he said there were no
such errors last night. This is consistent with the
amount of free space in
the shared_pool, but why am I getting the trace
files? Anybody else seen
this? I am on 8.1.6.3
Thanks.
Henry
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Henry Poras
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
__
Do You Yahoo!?
Get email alerts NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: A. Bardeen
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Henry Poras
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line
