Re: Solution for 01555

"vlad" <bulk_at_sfatcu.com> wrote in message news:xR8X9.77625$1q3.12213_at_sccrnsc01...
> 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.

Actually, every "statement" does indeed get assigned an SCN, at parse time, in the library cache. That's how come if you ask for a big select at 10.00am, and it finishes at 10:30am, your last row returned is consistent with the first one: because the entire select statement got assigned an SCN at the time it was first parsed.

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

Not really. You'd have to do real-time updates of each SCN of every buffer in the buffer cache. It doesn't bear thinking about. The 'optimistic' approach is to rollback a buffer on an 'as needed' basis. And that precludes knowing about it ahead of time.

Bear in mind: how do you fit flashback into this scheme? I submit a query at 10.00am "as of timestamp 6:00am". Now Oracle couldn't possibly have predicted that 4 hours later I would throw out a query as of 4 hours ago. What's it supposed to do: hang on to the stuff just in case?

And yes, I know this is a 9i feature. But it's a feature based on 6/7/8/8i core functionality that hasn't altered through the versions. Just shows what a sound model they came up with in the first place, if you ask me.

>Like you say, there
> may be some performance degradation here,

Some? Huge!

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

Yes, and that's certainly what happens. Lots and lots of read-consistent gets. But all it takes is CPU cycles and nanosecond memory access. The cost is relatively trivial.

Regards
HJR
>
> 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 - 04:44:44 CST