RE: FW: ORA-01555 - Delayed Block Cleanout

Hi Barb...

  Check out Note:45895.1 cause 3... I think this was the same one Jared posted the other day... I've posted part of the note below with an example that relates to your question...

Cause #3:

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

        ( Here is the large update - For arguments sake lets say SCN = 990 )   

    (ii) A long query begins against the previously updated blocks.

        ( Here is the FTS you are performing to "cleanup" - Read consistent SCN needs to be 1000 )   

    (iii) During the query, a considerable amount of DML takes place,     though not on the previously updated blocks which the query is     currently fetching.

        ( Other sessions are performing DML but not on the table you are interested in - Let's say a SCN of 1010 )   

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

         ( Not good )   

    (v) Under condition (iv), the commit SCN of the first update is     cycled out of the rollback segment.

         ( You know where this is going )   

    (vi) Under condition (iv) the lowest SCN in the rollback segment is     pushed higher than the read consistent SCN in the query.

         ( Bingo... - Rollback only knows about SCN's greater then 1005 due to this activity )   

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.

         ( Your read consistent SCN is 1000 but the rollback no longer contains this information )

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.

         ( Read consistency 101 )

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.

         ( The upper bound would be 1005 in this example )

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.

         ( This is not the case in my example since the read consistent SCN of 1000 is greater then the SCN of 1005 that is currently available in rollback )

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.

         ( Since the lowest value SCN in rollback is 1005, we know that the block was committed before SCN 1005... But we do not know if the block committed before or after the start of the query at SCN 1000... Therefore, Oracle can not guarantee read consistency since it does not know if the block it is currently looking at changed before or after it started the query and therefore issues a snapshot too old )

HTH
Tim     

-----Original Message-----
Sent: Monday, January 28, 2002 3:57 PM
To: Multiple recipients of list ORACLE-L

OK, I'm moving this question back to the list, since my understanding of delayed block cleanout is so weak.

Here's the definition I found of delayed block cleanout:

        When a data or index block is modified in the database and the transaction committed, Oracle does a fast commit by marking the transaction as committed in the rollback segment header but does not clean the datablocks that were modified. The next transaction which does a select on the modified blocks will do the actual cleanout of the block. This is known as a delayed block cleanout.

According to this definition, your problem cannot be delayed block cleanout unless a data block has been modified.

If delayed block cleanout is the problem, the recommended solution is FTS before you start your query. (Note 40689.1: If it is suspected that the block cleanout variant is the cause, then force block cleanout to occur prior to the transaction that returns the ora-1555. .. [set to rule and select count(*), or don't change optimizer and select count(*) with full hint]

Forcing this FTS should not cause an ora-1555, because you have not modified any blocks.

Here's where someone on the list can enlighten me.

In your case, I believe you're doing the large data load, then the transaction completes. The dataload is followed by a query against the table as a separate transaction. I'm guessing that the FTS will NOT cause a 1555 because it's query only, and will resolve any outstanding block issues. I would think the follow-on FTS would only cause a 1555 if it was running at the same time as the original transaction that's loading the data.

Can anyone on the list confirm this??

Barb

> ----------
> From: Walter K[SMTP:alden14004_at_yahoo.com]
> Sent: Monday, January 28, 2002 12:34 PM
> To: Baker, Barbara
> Subject: Re: FW: ORA-01555 - Delayed Block Cleanout
>
> No, it's the same issue as before except I am trying
> to come up with a way of preventing the 1555 error.
> According to my understanding, 1555 due to delayed
> block cleanout occurs when a block is left as
> "uncommitted", and the corresponding rollback segment
> block, which hopefully contains the SCN for when the
> block was committed isn't available because the
> segment wrapped and the block is no longer in the
> rollback segment, thus causing the 1555 error.
>
> So, according to what I have read, until all blocks
> are read, then and only then, or if the DB is bounced,
> will the blocks get cleaned out (marked committed).
> So, if the FTS fails due to 1555 it would seem to me
> that it failed on the first block that it encountered
> a problem with, that block gets cleaned but no more
> blocks are read in because of the error and therefore
> it is possible for the remaining unscanned blocks to
> still be flagged as "uncommitted" and the 1555 will
> keep occurring until every block has been scanned. If
> this is in fact the case then one may need to perform
> the FTS numerous times until all the blocks have been
> scanned successfully. This is obviously not practical
> and is what I am trying to get to the bottom of.
>
> The table in question is 20 million rows large, is
> truncated and loaded weekly, and no DML is ever
> performed on it. We put an exclusive lock on the table
> to ensure the 1555 wasn't the result of concurrent DML
> occurring. I am pretty confident that the 1555 we have
> seen is due to delayed block cleanout but again, if
> the solution is a FTS to clean the blocks out it would
> seem to me that a FTS could need to be done several
> times until a clean select can be performed. I'm not
> sure if the ANALYZE...COMPUTE that Jared suggested
> would fail due to 1555, if not, it would be a better
> alternative than multiple FTS's.
>
> Does this make sense?
>
> -w
>
>
>
> --- "Baker, Barbara"
> <bbaker_at_denvernewspaperagency.com> wrote:
> > I confess to knowing little about delayed block
> > cleanout.
> > However, if you get a 1555 error when doing a fts,
> > then I assume someone is
> > doing something other than querying the table at the
> > same time you're doing
> > the fts.
> >
> > I thought this table was going into a warehouse. Is
> > there really that much
> > activity on the table that you can't do a fts
> > without someone else changing
> > data on you at the same time? Or am I missing
> > something??
> > Barb
> >
> >
> > > ----------
> > > From: Walter K[SMTP:alden14004_at_YAHOO.COM]
> > > Reply To: ORACLE-L_at_fatcity.com
> > > Sent: Monday, January 28, 2002 8:55 AM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: ORA-01555 - Delayed Block Cleanout
> > >
> > > From what I have read, a full table scan may be
> > > necessary to initiate block cleanout in a VLDB to
> > > prevent ORA-01555 errors due to delayed block
> > > cleanout. However, if the full table scan itself
> > falls
> > > victim to the ORA-01555 error, how does one
> > guarantee
> > > that all the blocks in the table are
> > visited?--keep
> > > running the FTS query? The parameter
> > > delayed_logging_block_cleanout is not an option as
> > it
> > > is no longer available in my DB (8.1.7.2
> > > comp=8.1.6.3).
> > >
> > > Thanks.
> > > -w
> > >
> > > __________________________________________________
> > > 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: Walter K
> > > INET: alden14004_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).
> > >
>
>
> __________________________________________________
> 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: Baker, Barbara
  INET: bbaker_at_denvernewspaperagency.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Johnston, Tim
  INET: TJohnston_at_quallaby.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).