Re: Excessive Logical and Physical I/O

"Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message news:4061D450.ECB68731_at_remove_spam.peasland.com...
> > > Building a read consistent image is on a block by block basis. If one
> > > user only needs 1MB of read consistent images, then they only rebuild
> > > the number of blocks to satisfy that request.
> > >
> > > HTH,
> > > Brian
> >
> > Does that mean that rollbacks that are allowed to overextend don't
> > have a negative impact on performance...? because everything I have
> > read points to the opposite...
>
> I'm having a hard time understanding how you went from my comments to
> your comments. I must have missed the logic somewhere along the lines
> there.
>
> And what do you mean by "overextend"? A rollback segment can extend, up
> to MAXEXTENTS. It can't extend past that. MAXEXTENTS is the maximum
> number of extents. How can you go over this amount?
>
> Any time an extent has to be allocated or deallocated, work has to be
> done. If your task requires an allocation or deallocation of an extent,
> then the time it takes to complete the task will be longer than if you
> were able to accomplish the same task without the
> allocation/deallocation of that extent. Will that have a negative impact
> on performance?

I don't normally disagree with Brian, but I think he's missed the point here. The problem with rollback segments extending so that they are very big is not in the business of the actual extent allocation. Especially not if you've housed your rollback segments (and I do mean *rollback* segments) in locally managed tablespace. But even in dictionary-managed tablespace, extent allocation is only a major drag if lots of segments do it near-simultaneously, and hence you have a contention problem on UET$ and FET$. 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.

That's why Oracle invented optimal, of course, though I don't have a lot of good feelings about using it.

Where I do agree with Brian is that you don't really have a performance problem unless someone is noticing (and complaining about) it.

Regards
HJR
>It depends on many factors. I don't subscribe to the
> theory that just because something takes a little longer, it negatively
> impacts performance. To me, negative (or positive) performance impacts
> are measured by user pain (or joy) over the job that they are trying to
> accomplish. If the user doesn't notice anything, and therefore they are
> not experiencing any pain, then the allocation of extents is not causing
> a performance problem. If you stop the allocation of extents, and the
> user is happy with the results, because it is a noticable improvement,
> then there was a negative performance impact.
>
> HTH,
> Brian
Received on Wed Mar 24 2004 - 19:03:39 CST