Re: Do I really need more than 1 rollback segment?

I am a little suspicious of the 'sequential writes' strategy - only insofar as it may be pointless, rather than counterproductive - from a statistical viewpoint.

If you have 20 rollback segments (which seems to be a fairly common scale of figure) - then recent transactions will be spread across all 20 rollback segments, and each segment will tend to have a 'hot window' of blocks rotating around the segment that represents the rough location of current transactions.

Given the existence of checkpoint chains, and the newer checkpoint timeout/interval algorithms, dirty blocks are trickled out to disc in order of first dirtying, and in little clumps.

Add in the factor that with multiple db write latches, and facilities for multiple db writers etc, there may be several working data sets, operating independently to maintain their checkpoint chains, then:

At any one instance, the number of consecutive blocks to be written to a given rollback segment is likely to be rather small. The only exception to this generalisation is the moment when a proper checkpoint takes place.

There are now some statistics (name like: undo checkpoint blocks written, undo blocks written) which allow you to determine how much writing took place due to the trickle or forced writes, and how much was due to checkpointing.

Your 14% is interesting - following checkpoint/non-checkpoint analysis, and after determining whether there is undue wait time due to I/O, you MAY find that reducing the size of your rollback could improve performance by reducing the total I/O load on the system.

The 0.04% suggests you have some relatively long running transactions on the system, although the average read time suggests that these are being read from buffer, and therefore could be blocks which are re-read just fractions of a second after they are written. Half a dozen BBWs on the RBS is, of course, totally ignorable - and may have nothing to do with reading anyway, especially if they are against segment header blocks.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

andrew_webby at hotmail wrote in message ...


>For what it's worth, I find in my main production DB that rollback

>reads account for 0.04% of reads, and 14% of writes. Av read(ms) =

>0.2. I get like half a dozen buffer waits (avg wait = 0ms) on RBS

>tablespace during 2 peak hours.

>

>Also, while the main database files reside on a Sun T3 array, the

>rollback tablespaces are on the old SSA114's thus guaranteeing a clear

>path for sequential writes.

>

>The application does generate an horrific amount of redo and

>consequent rollback - which we're assured the latest version will

>cure. As if...

>

>"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message

news:<3c197f11$0$7497$afc38c87_at_news.optusnet.com.au>...


>> Comments below

>> HJR

>> ----------------------------------------------

>> Resources for Oracle: http://www.hjrdba.com

>> ===============================

>>

>> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message

>> news:1008285742.1773.0.nnrp-13.9e984b29_at_news.demon.co.uk...

>> >

>>  [snip some good stuff]

>> >

>> > Stats from v$rollstat - virtually no difference.

>> > Stats from v$filestat -

>> >     Small rbs    50 blocks written

>> >     Large rbs    686 blocks written.

>> >

>> > If your rollback segments are small enough

>> > they will be recycled before Oracle has time

>> > to dump them to disc - and this can make

>> > a dramatic difference on the total I/O - which

>> > will affect the way DBWR can work even if the

>> > RBS is on a totally separate set of discs.

>> >

>>

>> Makes sense.  It seems to me that you are trading I/O against risk of

1555s


>> (though I note your qualification below).  Normally I would run a mile

from


>> extra I/O -but this one I have doubts about.

>>

>> Furthermore, since DBWR does writes in the background, I'd be interested

to


>> know the actual performance impact of this particular bit of extra I/O.

I


>> guess you wouldn't agree with Steve's assertion that "It is not critical

to


>> optimize rollback segment writes because they are performed in the

>> background by DBWn."?  Or that "Rollback segment I/O is normatively

>> write-intensive and largely logically sequential. If the rollback segment

>> requirements are large enough, it is possible to obtain largely

physically


>> sequential I/O by placing each rollback segment in its own tablespace and

on


>> dedicated disks."?

>>

>> As for Steve's statement that "There is no overhead in using large

rollback


>> segment extents instead of small ones. " -well, this can only be

explained,


>> given your figures, if he's assuming that the additional writes are

>> insignificant in the scheme of things.  Agree/Disagree?

>>

>> I presume from all this that you would hate the 9i algorithm for undo

>> segments, and particularly the undo_retention idea (on the grounds, I am

>> assuming, that the need to retain undo for, say, three hours, would force

>> such undo to be written to disk instead of being merely overwritten in

the


>> buffer cache???).

>>

>> Would relish your thoughts.

>>

>> Regards

>> HJR

>>

>>

>>

>>

>>

>>

>> > Having said that, I also point out in the seminar

>> > that whilst small is good, you have to size your

>> > total rollback to support your longest running

>> > read-consistent event.

>> >

>> >

>> > --

>> > Jonathan Lewis

>> > http://www.jlcomp.demon.co.uk

>> >

>> > Host to The Co-Operative Oracle Users' FAQ

>> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html

>> >

>> > Author of:

>> > Practical Oracle 8i: Building Efficient Databases

>> >

>> > Screen saver or Life saver: http://www.ud.com

>> > Use spare CPU to assist in cancer research.

>> >

>> > Howard J. Rogers wrote in message

>> > <3c166064$0$559$afc38c87_at_news.optusnet.com.au>...

>> > >We could have a battle of the giants on our hand here.  Steve Adams

says


>> > >make 'em big, and who cares about a bit of wasted space: these things

are


>> > >supposed to be on their own hard disk anyway.  (I am paraphrasing like

>> > >crazy, natch).

>> > >

>> > >Personally, I go for the Steve Adams school of thought on this one.  I

>>  can't

>> > >see any drawbacks (though I'm sure Jonathan will elaborate on the

>> > >'additional I/O' idea) of large segments.  It's the NUMBER of them

that's


>> > >the worry, to avoid contention issues.

>> > >

>> >

>> >

>> >