I'm too tired to bug you tonight....
- Jared.Still_at_radisys.com 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 <sfaroult_at_oriole.com>
> Sent by: root_at_fatcity.com
> 01/25/02 10:39 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> 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.
>
> Rajesh.Rao_at_chase.com 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" <bbaker_at_denvernewspaperagency.com>@fatcity.com on
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Jared.Still_at_radisys.com
>
> 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: ListGuru_at_fatcity.com (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: wisernet100_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Fri Jan 25 2002 - 18:26:32 CST
