Re: Can anyone explain this huge increase in logical I./O....?

> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited ----------
> ------------
> cursor: pin S wait on X 10 0.01
> 0.09
> SQL*Net message to client 638104 0.00
> 0.96
> SQL*Net message from client 638104 0.01
> 69.23
> latch: cache buffers chains 3 0.00
> 0.00
> db file sequential read 16 0.11
> 0.18
> cursor: pin S 6 0.00
> 0.00
> ********************************************************************************
>

> DB Two (slow):
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited ----------
> ------------
> cursor: pin S wait on X 2 0.01
> 0.01
> SQL*Net message to client 638104 0.01
> 0.97
> SQL*Net message from client 638104 0.04
> 112.61
> db file sequential read 22 0.03
> 0.05
> latch: cache buffers chains 2957 0.00
> 0.05
> buffer busy waits 11 0.00
> 0.00
> cursor: pin S 2 0.00
> 0.00
> latch free 1 0.00
> 0.00
> ********************************************************************************

In the "slow" database, you have many more waits for the 'cache buffer chains' latch, plus you have buffer busy waits. This tells me that in this system, you have much more concurrency for these resources. Is it possible that in the first, you were the only user accessing this data, but in the second database, there were multiple users accessing (and maybe modifying) the same data blocks?

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown