Re: Sporadic ORA-04030

Ed Stevens wrote:

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

I think you're walking down the right path and it is the app. But for now how about modifying the default profile and limiting resources by session so that the offending code doesn't bring down the whole thing.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)