Re: large PGA_AGGREGATE_TARGET value problems

Hi James I noticed the same thing recently with something else (that a big fat SELECT won't enter trace mode/details while spinning )

i.e. if you set it into trace initially before it executes the SQL it's OK but if you try it while it's spinning then it won't return any trcinfo if at all until after it returns

if you have a repeatable case you might try putting a logon trigger for 10046 and 10053 trace
"James McCudden" <james.mccudden_at_mckesson.com> wrote in message news:5b8af1c7.0401280712.4176b509_at_posting.google.com...
> Thanks. I forget to note that that is, in fact, our workaround.
> Using the manual memory settings with a big fat S_A_S allows the
> system to run fine. Oracle has now at least logged this is a bug in
> 9.2.0.4. Whether any resolution will happen (with 10g on the horizon)
> remains to be seen. It (the infinitely spinning process) is also not
> predicatable or repeatable (except in one case so far), which makes it
> tough to give them a test case.
> Also, when the shadow process is spinning, it does not respond to
> dbms_system.set_ev() commands to show the event trace.
>
> James
>
> "Telemachus" <Zaka_at_twibbles.99.net> wrote in message
news:<ZvvRb.376$rb.51715_at_news.indigo.ie>...
> > 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
Received on Wed Jan 28 2004 - 10:27:31 CST