Re: Solution for 01555

I'm not sure why you say that "you'd have to do real-time updates of each SCN of every buffer in the buffer cache". If you are talking about the rollback buffers, I would assume that they get an SCN at the time the DML was executed that caused the creation of the rollback. If you mean the datafile buffers, those buffers should have an SCN as written into them by the last DML that modified them.

Hey, the question about flashback is completely unfair. That's not something a database is required to support. It's one those cool things you can do with Oracle. A database is not supposed to give you the history or the future of a record. It IS, howewer, supposed to tell me what's in the table right now without giving me some "snapshot error". I'm asking a fair question, and I should get an answer.

My opinion is that Oracle decided on this architecture but did not take the time to fully implement it, but rather passed the bad consequences onto its users. If I program this cool database that can do all these wonderful things, but one in 1000 queries gives you an error "Block too hard to find", well, that wouldn't be very cool, would it?

Now, I understand, that given the time and resource limitations we all live under, Oracle maybe couldn't do all this and still charge a reasonable price for its software. Still, an architecture liable to abstruse errors like "delayed block cleanout" simply sounds incomplete to me.

vlad

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:Km9X9.29448$jM5.76332_at_newsfeeds.bigpond.com...
>
> "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 - 23:09:13 CST