Re: Solution for 01555

Thanks Howard for your response. Obviously you are way more knowledgeable about this stuff than me. Let me try a response.

OK, I was loose in terms I used in my "solution". I guess I'm still trying to put all these terms and concepts together. Let's say we get the SCN at the start of the oldest transaction. Or say every statement registers its SCN into a global table. However exactly it is implemented, there must be some way (in my opinion) for Oracle to determine if somebody might be scanning a table asking for a block with an older SCN. Like you say, there may be some performance degradation here, but I'm sure the same can be said for the multi-version concurrency model (old blocks may have to be repetitively loaded and reconstructed in the SGA to give read consistent views).

You say disk space is cheap. Well, not if you need to buy fiber channel hard-drives from Compaq and if your drives are RAID'ed! If there was some way to determine when undo is not needed, we could overwrite it as soon as possible and save disk space.

I'm still struggling with the delayed block cleanout problem. I don't quite see why, if we don't overwrite rollback segments needed by an active transaction (or select), we woudn't be also preventing delayed block cleanout.

vlad

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:yt7X9.29333$jM5.75991_at_newsfeeds.bigpond.com...
>
> "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 - 03:58:53 CST