FW: FW: ORA-01555 - Delayed Block Cleanout

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