| 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: set event 4031
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,
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen INET: abardeen1_at_yahoo.com 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: ListGuru_at_fatcity.com (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: Henry.Poras_at_ctp.com 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: ListGuru_at_fatcity.com (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).Received on Tue Sep 04 2001 - 16:07:43 CDT
 |
 |