Re: Very Interested in Common Rollback Issue

Ok, I sort of get it. I can see how if we are looking for consistent blocks rather
than rows, and things may not neccesarrily be ordered that neatly to begin with,
but am still confused about the process you outline below about rolling back the deleted rows. If I start his loop at SCN 5000, get 80,000 rows into it ( which is what actually happens in my case), and then my cursor loop is looking for a block (tries the buffer cache first I assume), and notices it was stamped

at 5050, do I really "rollback transaction 5050" - etc., I mean, it was already comitted wasn't it? Don't I really look in the rollback segment
for the block I'm looking for less than 5050, find one for 5023, try again, and

then fail to find one for 5000 and send out the error? (Maybe just a re-intepretation).

In any case, if I'm close to understanding you - what do you think of the follwing suggestions to get around this problem -

1. Take the commit out of the loop - I think this would work, but may result in a lot of rollback extension - your thoughts?
2. Don't do any deletes, just throw the rowid's into a table and after the loop is finished, clear out the load table. - This seems like the best suggestion to me, what do you think? or
3. Add some rollback segments - (This was suggested to me ) - I think this is unlikely to help, and if it does, it would only be by chance since the commits are invalidating the rollback entries, regardless of what segment they're in. Adding rollbacks only seems like a good idea if I get a lot of redo header contention, which it would seem to me has nothing to do with this problem... your thoughts?

Thank you J. Lewis for your time,
- Dc.

Jonathan Lewis wrote:

> The key point is the error in your comment:
>
> >If this is the only transaction on the box then WHY do we need to
> >reconstruct the
>
> Each commit terminates a transaction. There are lots of transactions
> going on on the box. (Really the cursor driving the loop should become
> invalid the first time you commit, and then the 1555 couldn't happen
> because the loop would break, of course).
>
> The actual failure of read-consistency in this circumstance is caused
> in part by the ORDER BY in the driving loop, in part by the probable
> existence of indexes and finally by the fact that it is the BLOCK
> that needs to become read consistent, it has nothing to do with the
> specific row.
>
> Consider:
> I am at step 1000 of the loop
> The loop started at system commit number 5000
>
> I got to block X which says the last commit that changed the block was
> 5050
>
> The row that I want to use APPEARS to be there, but how do I know it is
> the same row that it was when I started the loop ?
>
> So I rollback transaction 5050 (which entails rolling back one deleted
> row, and
> updating the ITL entry in the block, and getting the entire block
> stepped backwards
> by one transaction).
>
> The new (older) version of the block now says that it was last changed
> by a
> transaction with a system commit number of 5023. The row I want is
> still there,
> and it still APPEARS to be the row IO expect but still it is possible
> that a transaction
> before 5023 changed it (or one deleted it and another put it back) - so
> I roll back again
>
> etc.
>
> until I roll the block back to an SCN prior to 5000, or I find that I
> can't find the
> rollback that I need and raise 1555.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> Doug Cowles wrote in message <378F5BCE.B5435B4F_at_bigfoot.com>...
> >A recent posting, from Nick Rumble (if he wants credit), which also
> >reflects something happening on my account, indicated the following.
> >If you are engaged in the following logic and are encountering a
> >snapshot too old -
> >Direct Splice ---------------
> >"
> > for x in ( select * from transaction_table where ... order by .... )
> > loop
> > update some_other_table;
> > delete from transaction_table where key = x.key;
> > commit;
> > end loop;
> >
> >
> >
> >I'm willing to accept this as the truth but I'm wondering if I can get
> >more detail.
> >If this is the only transaction on the box then WHY do we need to
> >reconstruct the
> >transaction table. If we are selecting one row after another, and after
> >we delete
> >a row we no longer need, at what point do we need to reconstruct that
> >and generate
> >a snapshot too old? I could see if someone else needed a read
> >consistent view of the table but there is no one else on the box. The
> >cursor is only opened once, correct?
> >So after it is open, rollback is generated by the update logic, and
> >thrown out after
> >commits, but what would cause the desire to re-construct something that
> >has been
> >tossed off as done with?
> >
> >- Dc.
> >
Received on Sat Jul 17 1999 - 19:39:04 CDT