| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: optimal size for rollback
Hi Connor,
The point you make regarding IO is important. For the benefit of anyone who is vaguely interested ...
By ensuring that RBSs are as *small* as possible but without excessive extend and shrinks (too small) and without ORA-1555 (again too small) and by having as *few* RBSs as possible without excessive contention (too few) or ORA-1555 (again too few) you enable "rollback caching". Rollback caching is basically having few, small RBSs such that all rollback blocks can be stored or cached in memory. Before they get the chance to be aged and written to disk, they get *reused* in memory.
A few key points.
Rollback caching *does not* generate savings in *physical reads*. That is no matter how big, small, many or few RBSs you have, the number of physical reads is generally unaffected per se. It certainly affects the likelihood of ORA-1555s but in terms of I/O behaviour, the influencing factors are size of buffer cache and access characteristics. Why ? Because if a rollback block is required for writing purposes (i.e. a transaction needs to write to a rollback block), if the rollback block is already in memory, great Oracle will reuse the buffer. If not, Oracle simply creates the rollback block in memory (as there's no need to physically read the rollback block from disk as all details are to be overwritten anyway). Oracle 'saves' the I/O by grabbing a free buffer and 'generating' the block in memory. If a block is required for reading purposes (e.g. for a rollback operation or for consistent read purposes) the block will have aged in the buffer cache LRU the same regardless of how big the RBS itself is. If it's still in memory, it was a somewhat recent piece of rollback, if it's no longer in memory then it was a rollback from somewhat longer ago. However its chances of being cached are identical. A larger RBS delays it being overwritten (and hence prevents ORA-1555s). It doesn't mean it's more or less likely to be in memory as the same number of rollback blocks are generated between the rollback block being changed and the current point of time, regardless of the size of the RBSs. That is, if 'x' number of blocks can be cached with small RBSs, then at least 'x' number of the most recently modified blocks will be cached with large RBSs as well. Remember, if the RBS is too small and we get to the specific rollback block extent too soon (before its commit/rollback), Oracle will extend the segment. This however doesn't change how an individual rollback block is aged.
So in summary, size of RBSs does not affect the number of physical reads per se.
However what it most certainly does affect is the number of *physical writes*. Why ? Because if you have small, few, cached RBSs (i.e. they don't age out) then they don't get written to disk (except at a checkpoint). *Reduced physical I/Os* is the result. The rollback blocks get reused before aging and being written to disk (reincarnation before you actually die if you like :) But if RBSs are large and many, then a particular rollback block (which is dirty by definition) must eventually age out. This means that all rollback blocks must eventually be written to disk because the blocks can not stay in memory for a sufficiently long enough period before being reused (there's just too many of the buggers). Most of these physical writes are "wasted" in that they are unlikely to ever be read back into memory. Remember, they are only re-read back in for consistent read, rollback or recovery purposes. If we did need access to them for such a long periods, then we can't get away with small/few rollback segments (as either extends or ORA-1555s would occur) so rollback caching is not possible/appropriate.
You can easily tell if you have issues with these "unnecessary" writes in that the I/O statistics for the rollback data files will show high numbers of physical writes but low numbers of physical reads. The "difference" is basically the unnecessary I/O you generate by having excessively sized RBSs. So larger RBSs induce extra, 'unnecessary' physical writes compared to smaller, more cached RBSs.
There are two disadvantages to this theory. One, by sizing RBS to be sufficiently small to be cached you substantially increase the chances of ORA-1555 errors. If they occur too frequently, then obviously the RBSs need to be increased in size and/or number and rollback caching is not appropriate. However, if long running queries are rare or run at appropriate quiet times, then it's quite feasible. Two, checkpoints cause the rollback blocks to be written to disk regardless. However, if checkpoints are tuned effectively, substantial reductions in rollback I/O could still be achieved.
A performance consideration worth considering .
Cheers
Richard
"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
news:3D8F5F83.7DBD_at_yahoo.com...
> Niall Litchfield wrote:
> >
> > "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> > news:q9jj9.37911$g9.108570_at_newsfeeds.bigpond.com...
> > > Hi Howard and Karen,
> >
> > > One last point. Note that automatic undo management does extra work
'under
> > > the covers' which could be viewed as being sub-optimal. I haven't
heard
> > many
> > > that suggest using manual rollback segments is the way to go in 9i ...
> >
> > Connor certainly suggested that automatic undo management was
> >
> > 'basically an OPTIMAL clause
> > just under the covers...Its like "Oops, I'm an extent thats 3 seconds
> > over the retention limit...bang, I'm outta here"'
> >
> > which isn't exactly a ringing endorsement...
> >
> > And I must admit I am still sitting on the fence, at least partly
because I
> > don't really understand what AUM is actually doing, and partly because
> > sufficient equisized public rollback segments have never done me any
> > significant harm. Sizing them wrong or leaving a big or small rbs
segment
> > online when I didn't intend to on the other hand.
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
> > *****************************************
> > Please include version and platform
> > and SQL where applicable
> > It makes life easier and increases the
> > likelihood of a good answer
> > ******************************************
>
>
>
>
>
>
 |
 |