Re: Cache buffer chain problem

Doesn't make sense, does it? Why would a query suddenly process many many more rows?
Apart from that, why would it be waiting for a read on a particular block?

Frank

Doug Coan wrote:

> In article <8pjjik$di5$1_at_nnrp1.deja.com>,
> robertchung0909_at_my-deja.com wrote:
> >
> >
> > We have a server that is suddenly causing some problem. A job that
> > used to finish in about 3-4 seconds sometimes takes 5 minutes to
> > complete. This problem appears and disappears randomly. We found
 when
> > the job is taking long, it mostly sits idle waiting on "Cache Buffer
> > Chain". Has anyone seen this behavior? What should we do to
 alleviate
> > this problem? We are using Oracle 7.3.4 on RS/6000. Thank you in
> > advance.
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> One possibility:
>
> When things go bad, check the in v$sql and see if the number of
> logical_gets and/or rows_processed looks out of wack. I have seen
> queries do a few thousand rows_processed one time and billions of rows
> the next using the exact same query plan. The number of Logical i/o per
> second goes through the roof and the system is just thrashing memory. I
> have never been able to pin down the cause however i have done the
> the following and the problem has always gone away.
>
> - Performed analyze compute statistics rather than estimate.
> - Set multiblock read count down. I know if this value is too high
> Oracle limits it, but i think (no proof) that the optimizer get confused
> when this is too high.
>
> I have seen and heard of this more in 8.0.x but once in 7.3.4....
>
> HTH.....
>
> --
> Doug Coan
> Oracle Certified Professional DBA
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Sep 16 2000 - 06:00:38 CDT