Re: Oracle slowed down

Poh Tien Lye wrote:
> was down to less than 0.20. However, the ratio does not improve even when I
> increase the
> db_buffer from 17MB to 38MB. The ratio still drop to 0.20 after 8 hrs of
> operation.

Forget that! BCHR means nothing other than as a symptom of something else potentially wrong. And 38Mb is way too small anyway to have any meaning for BCHR.

> A check on the system events and I can't find anything unusual.

Oh, I can:

> db file sequential read 113,492 0 19,134
> 0 19-APR-06
> db file scattered read 3,980,849 0 1,017,991
> 0 19-APR-06
My guess is:

Stats are totally out of whack if you're using the cost based optimiser and that has caused full table scans (FTS) all over the place. Hence those numbers.

Or if you use the old rule based optimiser, you may have lost an index unnoticed and now the system is spending all its time doing FTS.

Or a combination of the two above. Bottom line: too many FTS. A few is not bad, those above are way over the top.

Check out for statements in v$sqlarea with abnormal number of reads (logical and disk) and those are prime candidates for some EXPLAIN PLAN sessions to find which index is missing or not used.

Check out also for large logical reads with few disk reads, although most likely that won't yet be your case given the very small number of buffers. You also have too many sqlnet waits: check the sqlnet setup hasn't changed and someone has thrown in a slow DNS or NTS verification.

> Any help is appreciated. Thank you.

well you got some anyway. HTH, must run: plane to catch. Received on Fri Apr 21 2006 - 03:21:46 CDT