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

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.
> > >
> >
> >
> >
Received on Fri Dec 14 2001 - 04:51:37 CST