Re: Very Interested in Common Rollback Issue

 Very imformative...thanks... quick thing..maybe I'm pulling a blank.. what's the "ITL" you reference below?

Jonathan Lewis wrote:

> I'm beginning to lose track, it's a problem of a conversation
> with a 24 hour turn-around: so apologies if I start to repeat
> myself or (worse) contradict myself.
>
> Comments interleaved:
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> Doug Cowles wrote in message <379324F4.3AF75174_at_bigfoot.com>...
> >
> >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;
> >
> >- When I update some_other_table, some rollback is generated and then
> canceled
> >on the commit.
>
> Rollback is never 'cancelled'. It may eventually be overwritten because it
> is no longer being protected by a TX lock. The TX lock is released on
> COMMIT.
>
> > It's the DELETE that causes rollback information to
> be generated
> >for the same
> >table that I'm selecting from actively.
>
> Correct
>
> > So, question #1 - you said in my solution that if
> >I dumped the rowid's into a table for deleting outside the loop, I could
> still
> >get a 1555 >from the table being updated (some_other_table).. is that
> right?
>
> I was wrong when I said that - mixing and matching circumstances too
> much.
> The only way this could happen is if you did your long running update
> with a
> a single commit, and someone else was changing the 'other table' at the
> same time.
>
> If you did lots of commits in the middle of the loop, the 'other table
> would simply
> see a number of little changes in separate transactions that had nothing
> to do
> with each other. 1555 would not happen because of the 'other table'.
>
> Sorry to add to the confusion.
>
> >
> Is this
> >because after we already generated rollback for some_other_table, we may
> need to
> >access it again? This is what I don't understand, you said that -
> >
> >"The session makes of copy of the current block, and tries to rollback back
> >the changes in that copy - this is what 'consistent gets' are - Oracle
> >trying to produce a copy of a block that was consistent with a given point
> in
> >time."
> >
> >How can you do this? Are these changes in the copy sitting as rollback
> info
> >right
> >now? (Have they been committed?) So you're trying to create a
> read-consistent
> >image of the block for how it looked in the past? Where do you put this
> block,
> >in the
> >buffer cache?
> >
>
> Consistent read copies go in the buffer cache. Try this as SYS and see
> what you get: (Version 8.0.4/5 version of x$bh)
>
> select dbarfil, dbablk,count(*) from x$bh
> group by dbarfil, dbablk
> having count(*) > 1
>
> You will probably find that there are a few blocks with multiple
> copies in the db_block_buffer. One will be the current copy, the
> others will be consistent read (i.e. copies with rollback reversed
> into them).
>
> When a session no longer needs a read-consistent copy of a block
> it will be flushed out of the buffer by the normal LRU algorithm. The
> important thing, though, is that it is highly transient. Only the
> single
> CURRENT copy will ever be updated by new, real, changes.
>
> >I'm distinguishing block, from rollback info, since as you pointed out,
> they're
> >not
> >necessarily the same.
> >
>
> A sample (abbreviated) sequence of events.
>
> Process A starts a long running query.
>
> Process B makes a change to block 999 of table T and commits,
> this entails:
> Grab block 999 in current mode.
> Update row in block
> Update entry in the blocks 'ITL'
> Write entries into rollback block
> Write entries into redo log
> Commit which entails
> Write entries to redo log, force to disk
> Change state of rollback block to committed
> Change state of data block to committed (fast commit)
> Update SCN information in block
>
> Process A now hits block 999 in its query. Spots that the block is
> newer than the start SCN of the query, so makes read-consistent
> image which entails
> Grab free buffer
> Pin block 999 briefly and copy into free buffer
> (NB Does not change the block)
> Hunt for transaction, then rollback, indicated by most recent ITL
> Apply relevant rollback, to copy
> Which reverses the row change, and the ITL change, and the block SCN
> Check if SCN of copy is now consistent with start of query SCN
>
> Snapshot too old occurs when process A is unable to locate the
> rollback data required to build the read-consistent copy.
Received on Tue Jul 27 1999 - 20:05:14 CDT