Re: question about automatic undo management

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<b0lomg$3ph$1$8302bc10_at_news.demon.co.uk>...
> The most important point to make is that I think
> that many systems won't notice the difference
> between manual and automatic undo.
>
> However, for high performance system, I would
> almost certainly advise against automatic undo,
> and I would most certainly advise against allowing
> anyone to use flashback.
>
> There is no big secret - I've described the experiment
> at least once on this newsgroup, and I think I've
> seen someone else give a detailed explanation.
>
> Undo segments are reused cyclically, and if an undo
> block is acquired for reuse before it has been written, it
> can be 'new'ed without being written. Consequently
> if you can keep your undo segments small enough to
> ensure that you re-use them very rapidly during peak
> processing then you avoid lots of redundant writes.
> Designing an experiment to demonstrate the point
> is quite straightforward.
>
> Clearly, though, if the I/O activity on your undo tablespace
> is a small fraction of the total undo of the system, there
> is little point in getting to steamed up about trying to
> fix the undo sizing. Moreover, given the effect of incremental
> checkpointing (and I hear some people are suggesting a
> ludicrously low figure such as three seconds as a good time-out)
> it may be that it simply won't be relevant to consider the
> potential benefits of manual undo until other issues have
> been addressed.
>
> For the record, my best hit of a major production system
> was a net drop in the I/O load of about 30% by resizing the
> rollback segments. I also routinely present the following
> set of results from a simple test case at the seminar:
>
> I/O activity whilst using large rollback segments
>
> Filename Writes
> RBS01.DBF 686
> USERS01.DBF 1857
>
> Equivalent I/O activity whilst using small rollback segments
> Filename Writes
> RBS01.DBF 50
> USERS01.DBF 1813
>

I just wonder if these numbers depend on the buffer cache size? And if so, then how?

>
> A question you may be able to answer about automatic undo.
> It's a simple experiment that I haven't done yet:
>
> Note that automatic undo goes into an autoallocate tablespace
>
> Assume undo_retention is set low and typical undo size is 16 x 64K
> Due to a runaway process, the undo grows to
> 16 x 64K, 16 X 1MB, 2 x 8MB
> eventually smon kicks in and deletes all the oldest segments -
> leaving you with 2 x 8MB, which is 16 times as
> much as you need, but you cannot reduce it
> without bouncing the database.
>
> I've seem many systems where this scenario (possibly scaled up
> to the 64M / 256M boundary) looks as if it is waiting to happen.
> Can it happen, and does smon sort it out sensibly ?
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Coming soon a new one-day tutorial:
> Cost Based Optimisation
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____England______January 21/23
> ____USA_(CA, TX)_August
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
> Howard J. Rogers wrote in message ...
> >
> >I suspect we are going to go off into a deep, dark hole at this
> point.
> >Because as far as I can make out, flashback *requires* you to hold on
> to
> >whatever undo you think is useful. Now you're saying that doing so
> (which
> >means bigger-than-strictly-required segments) is an I/O nightmare.
> >
>
> Maybe.
> >But then it's a question of a very useful feature versus
> (questionable)
> >performance impacts. (questionable only because I haven't seen your
> >seminar). In the great scheme of things, I can't believe that an undo
> >segment of 200MB instead of 20MB is going to induce a 10x performance
> drop.
> >Yet it makes flashback 10x more useful. So I know which side I would
> prefer
> >to bat on.
> >
> >Without giving away your seminar secrets, this whole issue comes down
> (I
> >think) to 'do big rollback segments cause unnecessary I/O'. And, of
> so, how
> >much.
> >
> >I'm all ears.
> >
> >Regards
> >HJR
> >
> >
> >
Received on Wed Jan 22 2003 - 10:28:26 CST