Re: Excessive Logical and Physical I/O

> So the excessive reads were due to the query having to build a read
> consistent view of the table blocks.
>
> My next question is: Would large rollback segments (i.e. overextended)
> make this read consistent build process much slower..?

Because Oracle has to go through much more rollback information to build a read consistent image of the data! More rollback information to go through = larger rollback segments!

> The rollback segments have overextended to nearly 2Gb (2000 extents).
>
> Would this have a (noticeable) negative impact on the speed of the
> read consistent build process...?

Yes. If a user changes 1GB of data and a second user needs to build a read consistent image on that 1GB of data, it would take longer than if only 1MB of data was involved. More data = more work.

> The reason I ask this is because I have tested this out in our TEST
> environment and all other things being equal, the query runs very
> quickly in TEST.
>
> The only difference is that the rollback segments are much smaller in
> TEST (15 Mb). The size difference is due to an OPTIMAL setting in
> TEST.
Of course it ran quicker in TEST. You probably only have 1, 2, maybe 3 users in TEST, whereas PROD has many more concurrent users. And in many TEST databases, the amount of data is much, much smaller than it is in PROD. HTH,
Brian

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

Brian Peasland
dba_at_remove_spam.peasland.com

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


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