Re: question about automatic undo management

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:b0oesk$892$1$8300dec7_at_news.demon.co.uk...
>
> I would put the percentage of systems where it
> won't cause any observed performance problems
> much higher - somewhere in the 90's probably.

Good. I was hoping you'd say something like that. I think that's my point, and I'll be happy to leave the extreme cases out of it.

> Replacing an 'effectively untuned' rollback space
> with an 'effectively untunable' undo space shouldn't
> do anything for anyone - but there might be a
> demand for more space when the change is
> introduced.
>
>
> I think you are over-rating flashback. Bear in mind
> the undulate my data' is restricted to a five minute
> granularity, even if you quote the exact SCN (not
> that the typical end-user tends to check the SCN
> before starting a transaction ;).

I think 5 minutes could be plenty granularity for many situations. If you stuff up the edit of the record, seeing the record as it was 5 minutes ago is sufficient to reverse the error.

>As a very specialised
> tool for the extremely careful DBA, I can see it that
> it could be used as a replacement for a recovery.

Mmmm. The example we had here three days ago was a very non-careful DBA saving his ass using flashback as a pretty blunt instrument. But maybe that's an extreme case.

I'm not convinced you'd have to be that careful, either. User screws table at 9.15. You ask when did he do it. He replies, 'dunno, round about 9.00 o'clock'. To be on the safe side, you flashback to 8.45am. Sure, you've lost an unnecessary half an hour's worth of transactions on that one table, and maybe there are logical difficulties with other tables that now arise and need sorting. Better that, surely, in many cases, than losing half an hour's worth of data on the entire database, with loads of downtime and backup issues as a result of doing an incomplete recovery. And far less chance, I would have thought, of introducing logical corruptions than if you'd tried to use log miner. And less data lost than if you'd tried to import from last night's export.

> But then we have to move to a different area of
> argument - we ALWAYS have to trade between
> performance, recovery and cost. If you want to
> argue for UNDO because it reduces recovery time
> despite impacting performance, that's not the same
> as arguing UNDO because it avoids 1555 and is
> easier to administer.

I was arguing for undo because of a number of entirely separate benefits. Ease of configuration. No noticeable impact on 90%+ of databases out there. Greatly reduced 1555s. Possibility of avoiding expensive recoveries. Greater application functionality (trend analysis, anyone?). They're all different, but end up at the same conclusion.

>
> By the way - 1555's aren't necessarily a performance
> problem.

I was being flippant: when a report chugs along for an hour and then fails to produce any output, that's zero performance as far as that user is concerned.

>It's perfectly reasonable to ask an end-user
> questions like:
> Should report X be run in the afternoon, or do you
> want to stuff the system all morning long so that
> you can have it at lunch-time.
> If they tell you they want to stuff the system, you
> get it in writing and fax it to everyone that complains.
> If they say the afternoon is okay, then it's fine for
> them to get a 1555 if they try to sneak it into the
> queue at 9:30.

I agree that the ultimate, cheapest and best cure for 1555s is to alter user behaviour. Now which is easier: altering user behaviour, or slapping in a bigger undo_retention time?

>
> Good point about the reduction in UNDO I/O being
> 30% - the perceived impact on the system was
> that throughput was doubled. (Counterintuitive
> perhaps, but that's queueing theory for you when
> the system is close to the stress point). In fact,
> given that the doubling of throughput generated
> twice the UNDO - which didn't get written to
> disk, the saving on I/O due to the reduction in
> the UNDO size was actually double the apparent
> saving.

I think that was a point worth clarifying. Likewise that it's an extreme case (well, in the 10%-or-less bracket), though definitely real-world. It's like anything: you seek to find a rule that generally holds, but keep in the back of your mind the fact that there *are* exceptions, and you should be alert to the possibility that your application may happen to fall into one of them. I've no problem with that.

Nor with saying that automatic undo is not a free lunch (which would rather conflict with my usual advice that there are no free lunches in Oracle, cynic that I am). But if 90% of the time, the trade-off is disk space (and hence cash) versus ease-of-configuration/functionality/recovery options, I know which way I'd be leaning.

Incidentally, do you know any details about the modified allocation policy that automatic undo uses for transactions? Allegedly, it goes something like 'assign each transaction to its own segment, until you reach a point where you feel it appropriate to start sharing existing ones'... though a bit of testing this end (in an 8GB undo tablespace, so plenty of room for growth!) has indicated times when transactions 1 to 10 are assigned to their own pre-existing segments, transaction 11 shares an existing segment, and transaction 12 causes a new segment to be created. Transaction 13 then started sharing again. So it's rather more complicated than Oracle are letting on!

If a huge undo tablespace encourages the creation of lots of small segments, and no re-use of existing ones, then that might well be an issue to be considered, as you pointed out, that encourages greater I/O. But I'm getting mixed results, and I wondered if you had a more defintive rendition of the algorithm?

Regards
HJR Received on Thu Jan 23 2003 - 06:17:49 CST