Re: Excessive Logical and Physical I/O

Appologies,

My question wasn't very clear..

What I wanted to know was, do overextended segments have an impact on read consistency if they contain only a small amount of uncommited data.

For example:

Would it be much quicker to find 'one' consistent read block in small rollback than in a very large rollback..

I am trying to work out the overhead of segments that are allowed to overextend without shrinking back to a 'normal' size.

Matt

Brian Peasland <dba_at_remove_spam.peasland.com> wrote in message news:<4059B22C.FDA7A067_at_remove_spam.peasland.com>...
> > 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"
Received on Fri Mar 19 2004 - 02:49:13 CST