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:[EMAIL PROTECTED]]
> 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"
> <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]]
> > > Reply To: [EMAIL PROTECTED]
> > > 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: [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).
> > >
>
>
> __________________________________________________
> 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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnston, Tim
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).
