Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Can anyone explain this huge increase in logical I./O....?
mfullerton_at_gmail.com wrote:
> Slow DB has a significant number of cache buffer chain waits, possibly
> because it is fighting over blocks to read in the select that are being
> changed at the same time. Could be other blocks on the chain hogging
> the latch, but I doubt it.
>
> buffer busy waits: not a lot of time but more proof you are fighting
> over changing blocks.
>
>
This is the sort of thing that can sometimes be ameliorated by examing v$bh and using multiple buffer pools. It's difficult to tease out who is "really" hogging the latch, but the important concept here is that everybody affects what everybody else is doing. This also can lead to paradoxical "making the SGA bigger slows things down" issues, since not only are a few fighting over some hot blocks, but making it bigger means a bigger search to figure out which blocks are needed, and more checkpoint overhead.
This thread also illustrates how hard it can be to replicate performance problems outside a production environment.
jg
-- @home.com is bogus. "Slashing your tires is one thing, but hanging around to let you know why -- that takes balls!" - jawarrenReceived on Mon Oct 23 2006 - 16:57:43 CDT