Sporadic ORA-04030 , part tres

Platform: Ora 9.2.0.4 on Win2k

I wrote about this back in January, but we have much more detail now, and the problem has gotten 'hotter.'

The initial problem was seemingly random ora-4030 (out of process memory) when the app was run. We were unable to reproduce it on demand. Further work has revealed that we can reproduce it but only by running multiple instances of the application process in parallel. Currently, in the production environment, multiple instances are single threaded by scheduling their execution through Maestro. The next 'big event' for this app is to allow the users to kick of the process on demand. This leads to the potential of up to four instances running concurrently .. and possibly more than four in the future.

We have a tar open, but progress has been limited because the development team claims they don't have the time to spend on this problem! (Yet they ask for us to raise the priority of the tar with Oracle!) May partner has primary responsibility for this one, so I'm trying to research some other channels while he deals with the developers and the tar.

Looking back thru the alert logs for the last few weeks, I find dozens of occurances of this form of the ora-04030:

ORA-04030: out of process memory when trying to allocate ... bytes (joxcx callheap,ioc_allocate ufree)

and one occurance of this form:

ORA-04030: out of process memory when trying to allocate ... bytes (pga heap,ksm stack)

Searching for ora-4030, then for "joxcx callheap" (searched this ng, then MetaLink, then Google) there was a definiate trend toward indicating that the app was simply requireing too much memory. I'm not proficient in PL/SQL, particularly trying to analyze the nicities of a very large package body, but in looking thru it and discussing with the developers, I know that they are using some very large PL/SQL tables. They tell me they are very deliberate about releaseing them when they are through with them, but I'm suspecting they are simply too large to begin with. They tell me that they are loading 10k to 20k rows into them, and the rows are fairly large.

>From here I looked again at the physical memory constraints. We have
8gb of physical memory on this Win2k server, with the /3gb switch set. However, I saw a doc yesterday that indicated that even with this, the only thing Oracle will place above 4gb (this is a 32-bit system) is the buffer cache. If so, it seems that this would mean that the huge PGAs that the app is using would still be constrained to what's available below the 4gb line.

All of this to say that it is appearing to me that the app design is fundamentally flawed. If I'm right, I need to know how to come up with proof. If I'm not right, I need to get a course correction in my thinking. Received on Thu Apr 07 2005 - 10:02:06 CDT