Re: Increase the size of rollback segments

If the db_block_buffer is large enough, and the rollback segment small enough, and the transaction rate fast enough, it is possible for to get to the end of the rollback segment and go back to the start of the rollback segment and start re-using blocks before they have EVER been written to disc. If the rollback segments are arbitrarily large, it is much more likely that ageing, or checkpointing, will cause the older rollback segment blocks to be written to disc before they can be re-used.

(I believe that earlier versions of Oracle would not re-use rollback blocks without first writing them to disc, so this is a relatively new consideration.).

The benefit is likely to be highly dependent on the nature of the application, but I did manage to reduce the I/O done by dbwr processes on one 8.0.5 by over 30% by setting an optimal of 10MB on rollback segments, rather than letting them run at 200MB. (It is possible that your manual strategy would have made similar savings, but that also would be application dependent).

Of course, the biggest drawback to optimal appears if you make it too small, as Oracle always writes back the rollback blocks that it is going to release when it shrinks the segment, so you can get a massive overhead if your sizing is just a little bit off.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Howard J. Rogers wrote in message <39ed64c4_at_news.iprimus.com.au>...


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

>news:971808412.11363.0.nnrp-10.9e984b29_at_news.demon.co.uk...

>>

>> If you have a 1MB rollback segment, it may be

>> recycled several times without being written to

>> disc.  If you have a 100MB rollback segment

>> then you will probably write the whole thing

>> out before you get round to recycling it.

>>

>

>I'm probably being rather dense, but I can't quite see the logic here.

>

>You're saying small segments will never flush to disk, but big ones will in

>their entirety?

>

>Nope... Can't for the life of me see why that should be so.  Can you

>elaborate please, because I suspect I'm missing something fundamental here.

>

>Regards

>HJR

>

>

>

>>

>> --

>>

>> Jonathan Lewis

>> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

>>

>> Howard J. Rogers wrote in message <39ec377a_at_news.iprimus.com.au>...

>> >Can you explain what the IO cost is?  Cos I can't think of one.

>> >

>> >Regards

>> >HJR

>> >--

>>

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

>> >Opinions expressed are my own, and not those of Oracle Corporation

>> >Oracle DBA Resources:

 http://www.geocities.com/howardjr2000


>>

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

>> >

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

>> >news:971718991.1058.0.nnrp-08.9e984b29_at_news.demon.co.uk...

>> >>

>> >> The problem isn't one of space - like you, I am quite happy

>> >> to 'waste' empty space to make administration more straightforward.

>> >> In the case of rollback segments, though, there is an I/O cost

>> >> to large rollback segments that can be avoided by keeping the

>> >> rollback segments at the optimal size.

>> >>

>> >> --

>> >>

>> >> Jonathan Lewis

>> >> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

>>

>>

>>

>

>