Re: FW: FW: ORA-01555 - Delayed Block Cleanout

Barb,

As per my understanding of delayed block count, the definition that you give, is apt for Oracle 7.3 and below. Your statement that it does not clean the datablocks that were modified also holds true.

To add further, starting with Oracle 8, Oracle introduced a fast commit mechanism, whereby some of the data blocks are marked with the commit SCN. The number of data blocks that are marked as such, depends on the number of blocks that are updated. If the transaction is a short one, then all the blocks could be marked. However, if its a long transaction, then only a few of the blocks are marked. If a transaction is long or short, is determined by the number of blocks that are updated. The threshold value is 10% of db_block_buffers. Also, the blocks must be presently in the database buffer cache and not flushed out. If this is not so, then the old method of delayed block count takes place, where only the transaction entry in the rollback segment header is marked as commited.

And I believe, a snapshot too old could also occur because of some modifications to a data block, before the next transaction reading that block started.

Thats what I think. I could stand corrected. Insights??????

Raj

"Baker, Barbara" <bbaker_at_denvernewspaperagency.com>@fatcity.com on 01/28/2002 03:56:38 PM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:

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: 
  INET: Rajesh.Rao_at_chase.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).