RE: delayed block cleanout (?) for active tx

  • From: "Yasin Baskan" <yasin.baskan@xxxxxxxxxxxxxxxx>
  • To: <alberto.dellera@xxxxxxxxx>, "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 21 Jun 2007 16:39:05 +0300

I think what causes the redo is this:

"Suppose the ITL is still open and the transaction is not yet committed:

A reader on finding the ITL will create a consistent read copy of the
block because a view of the block before the change was made is
required. To make a CR copy, the undo from the rollback segment is
applied to he block."

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Alberto Dell'Era
Sent: Thursday, June 21, 2007 3:29 PM
To: Oracle-L
Subject: delayed block cleanout (?) for active tx

Dear all,

create table t as select rpad(rownum, 100) x from dual connect by
level <= 100000;
update t set x=x;

in another session:
set autotrace on
select count(x) from t;
 157612  redo size

If I select again - same redo size:

select count(x) from t;
 157612  redo size

Notice I haven't committed the update, so this is not the classic
redo generated due to classic delayed block cleanout (updates
to the ITL to mark the tx entries as "committed").

It is anyway related to cleanout, in fact v$mystat shows entries
such as:

active txn count during cleanout                       3031
cleanout - number of ktugct calls                     3031
immediate (CR) block cleanout applications      3031
db block changes                                           3031
cleanouts and rollbacks - consistent read gets  3031

Clearly the select has to build the consistent image of the
blocks, and clearly it has to visit the ITL, and then the rollback
segment, to see whether the tx has committed etc etc;
then, it seems that it updates "something" in the block
(db block changes). Does anybody know what this "something" is,
assuming of course my reasoning so far is correct ? on Windows XP.

Thanks in advance

Alberto Dell'Era
"the more you know, the faster you go"


Other related posts: