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