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 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).
