Re: Cache buffer chain problem

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.