Re: Excessive Logical and Physical I/O

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<406350ad$0$8359$afc38c87_at_news.optusnet.com.au>...
> "Joel Garry" <joel-garry_at_home.com> wrote in message
> news:91884734.0403251314.7bd1200a_at_posting.google.com...
> > Brian Peasland <dba_at_remove_spam.peasland.com> wrote in message
> news:<4062F9C2.D854684F_at_remove_spam.peasland.com>...
> > > > I don't normally disagree with Brian, but I think he's missed the
> point
> > >
> > > Feel free to disagree with me at any time. I don't mind. ;)
> > >
> > > If I need to be corrected, then I need to be corrected.
> > >
> > > > The problem is rather that if a rbs keeps extending, it keeps
> acquiring new
> > > > blocks instead of re-using old ones. And new blocks have to be read
> from
> > > > disk, rather than old ones being over-written in memory. Therefore,
> large
> > > > rollback segments are associated with more physical I/O than smaller
> ones.
> > > > And therein lies the real performance issue with rollback segments
> that keep
> > > > growing.
> > >
> > > Point taken. However, this is starting to show that there are many
> > > factors at work here. What if the blocks were in memory? Of course
> > > memory access is quicker than disk I/O and with larger rollback
> > > segments, you increase your chances that a block will not be in memory,
> > > but then we have to weigh in the algorithms that keep blocks in memory
> > > and how that contributes to your chances that what you need will be in
> > > memory when you need it.
> >
> > I'm having a bit of trouble with Howard's point. If the RBS is
> > growing, then it needs to grow.
>
> Obviously.
>
> > If it is growing because of bad
> > coding, that is the bad coding's fault. Once it has grown (which is
> > the crux of the question), then on the next transaction, what is the
> > difference between it being read in from this big RBS, or some other
> > RBS that would have been chosen?
>
> If it's a large rollback segment (say of 1000 blocks); and say each
> transaction only writes one block of undo; then it will take 1000
> transactions before you start re-using the first block of the rollback
> segment. And by the time you get to that point, it is quite likely that the
> 1st block will have aged out of the buffer cache. That block will therefore
> have to be re-read into the cache via a physical read.

My mistake. Somehow I thought that Oracle marked blocks in their headers when they were no longer needed, so they wouldn't need to be re-read to be given to some other transaction. Probably some other db.

>
> If it was only 100 blocks big, then only 100 transactions would be needed to
> take you back to the 1st block, and the chances are good that the block will
> still be in the buffer cache, not yet aged-out. Therefore, you will be able
> to over-write its contents via a logical I/O not a physical one.
>
> Obviously the situation gets more complicated in a real environment, with
> different sized transactions, and multiple simultaneous transactions, and
> multiple rollback segments, but the basic principle is sound: small segments
> recycle themselves faster, and therefore will be less likely to have aged
> out of the cache than big ones. Ergo, small ones are likely to have more
> logical, and less physical, I/O associated with them than big ones.
>
> But please don't do your usual trick of extending that observation to some
> simplistic advocacy on my part that all rollback segments should be
> peanut-sized. They need to be as big as needed, and that depends on how
> you've coded your transactions.
>
> >That's why I think it is often best
> > to have a large RBS TS, with a bunch of large RBS's sized to fit the
> > normal cases, and room to grow in there in case some new code goes
> > nuts. In that case manually shrink it, optimal just opens ORA-155x
> > doors.
>
> I don't disagree with having a large rollback tablespace with plenty of room
> to grow when needed (a large undo tablespace poses different problems,
> however). The tablespace isn't the issue. It's whether you have
> larger-than-needed rollback segments. I don't disagree either with
> preferring manual shrinking to automatic optimal.
>
> HJR
jg

--
@home.com is bogus.
"Why is there a doorbell if you can't ring it?" - Dennis the Menace