Re: question about automatic undo management

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

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

>

>

>