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.

Agreed. But there are always going to be the exceptions, as you point out, and then not being in configuration control is definitely going to be a problem, absolutely. Can we say that about 75% of databases should have no problem with 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.
>

Oh well, I knew we had to disagree somewhere! Flashback strikes me as being extremely useful, even in a high-performance system. We had an example just the other day: flashback to retreive your deleted rows, or perform a hideous incomplete recovery with all the downtime and data loss that implies. High performance doesn't usually mean 'to hell with committed data, I didn't much care for it anyway!'.

> 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.

Mmmmm. OK. But then does high performance really require the elimination of *writes*, which are done asynchronously anyway by DBWR. It's not as if my transaction, from the user's perspective, is waiting on the write. I'm not saying it's of no effect, but it seems a lesser matter to me than the convenience of flashback, ease of configuration (ie, no-brainer) of the undo tablespace and so on.

Provided the undo I/O is well away from the I/O on table/index data files, I can't see an excess of writes is too much hassle.

> 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.

OK, I don't doubt it... but what did this I/O reduction turn into in terms of responsiveness, elapsed time to perform a report, or transaction etc.

Incidentally, it seems to be that it's all fine and dandy to reduce undo writes, but you're doing it at the expense of over-writing undo blocks/buffers, and that must increase the risk of 1555s... which is a rather more serious 'performance' problem altogether.

> 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
Lovely. I buy the argument that small segments mean re-use, rather than flushes. But what are the costs v. benefits? What perceptible improvement in transaction processing speed do the fewer writes buy you, and what's the increase in the risk of 1555s? And how much use could your application make of flashback?

> 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 ?

Don't know, but I feel a testing session coming on.

'Course... I wouldn't worry about having 16 times as much undo as I need! But yes, for the extreme cases you're talking about where the excess write I/O might make perceptible performance impacts, then if this happened, it would be a problem. I'll see if I can have a look tonight.

Regards
HJR
>
>
> --
> 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 - 15:51:41 CST