Re: large PGA_AGGREGATE_TARGET value problems

Perhaps it thinks it can fit all of one table in memory and switches access plan accordingly ?

does use of SQL*Analyze show a difference when you play around with the value ? although I've a feeling you might need to do a workarea size policy =manual and watch the numbers yerselves for S_A_S,H_A_S,B_A_S

Alternatively there's always 10053 output to diagnose the optimizer choices although the reading is a little abstruse

"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...
>
> Thanks.
>
> James
Received on Tue Jan 27 2004 - 09:32:09 CST