Re: Solution for 01555

"vlad" <bulk_at_sfatcu.com> wrote in message news:Y17X9.74571$kH3.11417_at_sccrnsc03...
> I've been reading up on Oracle lately and find their multi-version
> concurrency model very intruiging. One of the downsides of this model,
> though, seems to be the dreaded "01555 snapshot too old" error. It seems
to
> me that this something Oracle should have handled this problem internally
so
> that users never get that error. I would propose something like this:
>
> Let's say we are about to overwrite some undo data.
> 1) Get the SCN of block whose data we're about to overwrite in the
rollback
> segment.
> 2) Compare to SCN of oldest active transaction.
> 3) If smaller, OK to overwrite.

Why?

If the oldest active *transaction* is 15000, and my query started at 14900, then on your basis, it's OK to overwrite undo from time 15000 onwards. But what about my select, which needs undo from time 14901?

>Otherwise, cannot overwrite. Must expand
> rollback segment and continue writing there.
>
> I would think that data needed for an active transaction's read consistent
> view should be just as holy as undo data that is need to rollback an
> uncommitted transaction.

OK... define 'transaction'. Selects need undo. Is a select a transaction?

And what you propose would mean, essentially, that every transaction would have to take time out to check what the oldest *select* statement was, before proceeding to over-write. It would be a perfomance nightmare, at least.

Besides which, it isn't difficult to totally prevent 1555's, as a quick trip to www.ixora.com.au will prove. You raise a dummy transaction in every rollback segment, and commit it (ie, roll it back) only when you're confident your select can proceed.

And, by the way, there's another cause of 1555s, to do with delayed block cleanouts, and your suggestion wouldn't reolve that at all.

>Don't touch it until you make sure no user will
> ever need it. Why couldn't something like this be done to avoid all the
> grief about "snapshot too old"?
>

See above. Fundamentally, there need be no grief about 1555s that a bit of disk space can't cure. UNDO_RETENTION is a gift from the Gods, but something similar *was* possible in earlier versions (see www.ixora.com.au) and fundamentally nothing deals with the delayed block cleanout issue, even now (though admittedly that accounts for something like 0.1% of all possible 1555s).

Ultimately, it's the old tradeoff: disk space versus performance. Disk space is cheap. Spend big. Then you don't have to worry 99.9% of the time.

Regards
HJR
>
Received on Tue Jan 21 2003 - 02:35:29 CST