large PGA_AGGREGATE_TARGET value problems

We are currently experiencing problems on systems where we set the PGA_AGGREGATE_TARGET up to a higher value (800MB or more). We ported a decision support (Data Warehouse) system from Sybase 1.5 years ago. Things have generally went well. In development and early customer use, the P_A_T value of 100-200MB seemed to give adequate performance.  More research and large real-life loads have made us increase this up to the recommended max of 40% of physical memory. Now, however, we are starting to see problems where the oracle shadow processes will, when processing a large multi-table join with lots of table/index scans and hash joins, just start running at 100% CPU and never returning. If the P_A_T is set lower, or if use manual memory mangement, the problem does not happen.

I'm working with Oracle on an open TAR for this, but thought I'd throw it out to the community to see if anyone else has exprienced this. Our systems are generally:

Tru64 unix, ver. 5.1A
4-8GB physical memory, plenty of swap
2-4 cpu systems
Oracle 9.2.0.2 (also happens in 9.2.0.4)
(doesn't/hasn't yet happended on on HP-UX 11.11 installs)

Our applications are all C code using the OCI calls. I have a feeling it's an issue with the P_A_T auto memory management under Tru64 for larger P_A_T values.

As an aside, out of curiosity, how many other people run a moderate
(100-400GB) data warehouse on Tru64 using essentially only OCI calls
in C for the applications? I'm starting to wonder if we are the only ones...

Thanks.

James Received on Tue Jan 27 2004 - 08:51:55 CST