I'm too tired to bug you tonight....
--- [EMAIL PROTECTED] wrote: > I was going to write this myself, but this explanation fron > MetaLink Note 45895.1 means I can just cut and paste, and > Rachel won't get after me for typos. :) > > Jared > > Delayed block cleanout on old committed updates. An update > operation > completes and commits; the updated blocks are not touched again > until a > long-running query begins. Delayed Block Cleanout (DBC) has never > been > done on the blocks. This can result in a scenario which happens > only > under specific circumstances in VLDB, causing ORA-01555 errors when > NO > updates or inserts are being committed on the same blocks a query > is > retrieving. > > All of the following must be true for an ORA-01555 to occur in this > > case: > > (i) An update completes and commits and the blocks are not > touched again until... > > (ii) A long query begins against the previously updated blocks. > > > (iii) During the query, a considerable amount of DML takes > place, > though not on the previously updated blocks which the query is > currently fetching. > > (iv) Under condition (iii) there is so much DML relative to > available > rollback space that the rollback segment used in the first > update > wraps around, probably several times. > > (v) Under condition (iv), the commit SCN of the first update is > > cycled out of the rollback segment. > > (vi) Under condition (iv) the lowest SCN in the rollback > segment is > pushed higher than the read consistent SCN in the query. > > (Note: The read consistent SCN is what the query uses to > construct > a read consistent view. Any block which has an SCN higher > than > this > was obviously updated after the query started and requires > rollback). > > The above conditions imply that when a query reaches a block that > has > been > updated but not cleaned out, the query quickly learns that the > update > committed, and accordingly cleans out the block. But because the > update > > SCN is no longer in the rollback segment (condition (v)), the query > > doesn't > know WHEN the update committed. This is important because if the > commit > > happened before the query began, the current value in the block can > be > used > by the query; but if the commit happened after, the old value must > be > fetched > from the rollback segment. Now, because the rollback segment > wrapped in > (iv), > we know that the update SCN can't be higher than the lowest SCN in > the > rollback segment, which gives us a nice upper bound. If we only > knew > that > the read consistent SCN was higher than this upper bound, we would > know > that > the update committed before the query started. But we don't know > this > because of condition (vi), so we can't even accurately "estimate" > the > update > SCN. Hence, we get an ORA-01555. > > > > > > Stephane Faroult <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 01/25/02 10:39 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject: Re: ORA-01555 Mystery (Help) > > > I was almost ready to subscribe to the idea of delayed cleanout, but > I > cannot understand why really. The necessity for reading a block from > the > rollback segments comes from encountering during the course of the > SELECT a block the SCN of which is higher than the SCN when the query > started. I have of course no certainty about it, but it would be > logical > to expect the block's SCN to be properly set irrespectively of the > clean-out being immediate or delayed. In other words, even if a > SELECT > physically writes blocks, it should not have anything to do with > rollback segments anyway. > I share Mladen's opinion, somebody must be economical with the truth > somewhere, and you should check V$ACCESS, V$SESSION and V$LOCK. Are > you > really sure that the code contains no 'just in case' commit ou > rollback > which would release the lock? And by the way, 5 hours look to me like > an > awfully long time, even for a 20 million row mega-select of death. > > [EMAIL PROTECTED] wrote: > > > > Precisely the point I was trying to make, when I put the question > if it > was > > a normal select, or if it was within a PL/SQL block? The myth is > that > > snapshot too old happens only when some other transaction was in > the > > process of performing an DML on a table, when you did a select on > it. It > > can happen for other reasons too. Search on Metalink for "Delayed > block > > cleanouts" and "fetch across commits". > > > > Raj > > > > "Baker, Barbara" <[EMAIL PROTECTED]>@fatcity.com on > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing > Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __________________________________________________ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
