Re: large PGA_AGGREGATE_TARGET value problems

"James McCudden" <james.mccudden_at_mckesson.com> wrote in message news:5b8af1c7.0401270651.4ddd395f_at_posting.google.com...
> 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...
>

Hi James

We encountered a "bug" (my term, Oracle Support deem it a feature) when using P_A_T on Tru64 5.1a whereby massive amounts of swap space was being consumed way in excess to the proportion of memory used (approx. 3G PGA used up all 30+ G of swap). This impacted CPU and prevented additional logons to the DB.

The fix was to set vm-swap-eager in /etc/sysconfigtab to 0 to disable eager swap mode and change to lazy (deferred) swap mode. The fault is 1 (eager).

I know you mention having plenty of swap, but originally we thought we had plenty as well.

Hope this helps

Richard Received on Wed Jan 28 2004 - 05:33:40 CST