Sporadic ORA-04030

Platform: Oracle 9.2.0.4.0 on Win2K

Database sporadically crashes with the following (from alert.log)

Errors in file e:\oradmin\abss\bdump\abss_dbw0_3356.trc: ORA-04030: out of process memory when trying to allocate 140628 bytes
(pga heap,ksm stack)

Tue Jan 18 15:10:09 2005
DBW0: terminating instance due to error 4030 Tue Jan 18 15:10:10 2005
Errors in file e:\oradmin\abss\bdump\abss_pmon_3200.trc: ORA-04030: out of process memory when trying to allocate bytes (,)

Instance terminated by DBW0, pid = 3356

We cannot reproduce the error on demand. During one of our attempts
(that did not fail) I did discover that one of the user sessions (the
one we were specifically concerned about) had up to 60 cursors open at one time. The query

SELECT SID,USER_NAME,SQL_TEXT FROM V$OPEN_CURSOR ORDER BY 1 returned up to 60 rows for the same sid.

Having read thru note 233869.1 - Diagnosing and Resolving ORA-4030 errors, we come down to "general suggestions". The firs is to use auto memory management. Unfortunately, we are not. WORKAREA_SIZE_POLICY=MANUAL and PGA_AGGREGATE_TARGET = 0. Point taken.

The second suggestion is to address the application. Am I wrong in thinking an app that holds this many open cursors is a problem? I've never had cause to look at this before and don't have a feel for what is "good" vs. "excessive."

BTW, the box has 7gb physical memory and 19gb total virtual memory.

Since the problem is sporadic, I'm not sure how we can even test any proposed solutions.

Thanks.

• Ed Stevens

Cohn's Law: The more time you spend in reporting on what you are doing, the less time you have to do anything. Stability is achieved when you spend all your time doing nothing but reporting on the nothing you are doing. Received on Fri Jan 21 2005 - 08:12:11 CST