Re: delayed block cleanout (?) for active tx
- From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
- To: "Shamsudeen, Riyaj" <RS2273@xxxxxxx>
- Date: Thu, 21 Jun 2007 23:38:43 +0200
Riyaj,
may you please check my reasonings below ?
In order to build a CR copy of the current block, Oracle has
to copy the current version to a new buffer, and then apply
the undo records to it. But the CR copy doesn't need to
be protected by redo - so the redo has to be generated
to protect some changes to the current buffer, not the changes
to the new buffer that holds the CR copy.
So I have blockdumped the current block [I have offlined/onlined
the tablespace containing table "T" to be sure to dump the current
version] and :
before "select count(x) from t;":
Start dump data blocks tsn: 9 file#: 9 minblk 1581 maxblk 1581
buffer tsn: 9 rdba: 0x0240062d (9/1581)
scn: 0x0000.0008d981 seq: 0x01 flg: 0x04 tail: 0xd9810601
frmt: 0x02 chkval: 0x67e8 type: 0x06=trans data
Block header dump: 0x0240062d
Object id on Block? Y
seg/obj: 0x7c8a csc: 0x00.8d981 itc: 3 flg: E typ: 1 - DATA
brn: 1 bdba: 0x2400621 ver: 0x01
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.0008775b
0x02 0x0006.010.0000011b 0x0080344d.007e.08 ---- 68 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
after "select count(x) from t;":
Start dump data blocks tsn: 9 file#: 9 minblk 1581 maxblk 1581
buffer tsn: 9 rdba: 0x0240062d (9/1581)
scn: 0x0000.0008eace seq: 0x01 flg: 0x04 tail: 0xeace0601
frmt: 0x02 chkval: 0x54a7 type: 0x06=trans data
Block header dump: 0x0240062d
Object id on Block? Y
seg/obj: 0x7c8a csc: 0x00.8eace itc: 3 flg: E typ: 1 - DATA
brn: 1 bdba: 0x2400621 ver: 0x01
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.0008775b
0x02 0x0006.010.0000011b 0x0080344d.007e.08 ---- 68 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
so - some fields of the current block have changed (scn, tail, chkval, csc),
and so their changes have been logged.
This could be described as a "null" block cleanout - Oracle found
nothing to be cleaned out and simply recorded the block as current
to an higher scn, possibly (my conjecture) to save some work on the
next block cleanout.
In fact Denny Koovakattu has told me offline (and I have reproduced)
that if you repeat the "select count(x) from t;" after setting the tx
as read only, the redo is generated only for the first execution;
the remaining ones show 0 redo size. This is consistent with
what said above, I think.
Thanks
Alberto
On 6/21/07, Shamsudeen, Riyaj <RS2273@xxxxxxx> wrote:
Alberto
I remember conducting similar tests. While creating a CR copy of
the block, undo records are applied to rollback the changes, so that a
specific version of the block can be created. Applying undo records
generates redo.
Rerunning the query has a different environment i.e. different
SCN requirements and this rollback need to be performed again. [ I
haven't tested this in 10g and I vaguely remember somebody saying that
there is an improvement in this area].
All statitics you see are due to this rollback. 'db block
changes' is incremented while applying undo records to the blocks too.
Thanks
Riyaj Shamsudeen
ERP financials DBA, New AT&T
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Alberto Dell'Era
Sent: Thursday, June 21, 2007 10:57 AM
To: Oracle-L
Subject: Re: delayed block cleanout (?) for active tx
On 6/21/07, *snipped name* wrote:
> The CR copy of the block should only be made if it's not available.
If the CR
> copy created initially is still available, the second one should not
have been
> created, right?
But the second one has to be consistent to a different (more recent)
point in time (higher scn), hence it has to inspect the current block
to check whether any modification has been performed in the meanwhile
(the active tx could have committed, even). Then, it may reuse the same
CR copy perhaps.
I've just re-checked everything and even after N selects,
the generated redo size comes out as exactly 157612 every time.
BTW The reason I'm performing this quest is that I have an
application that is generating excessive redo, and the only
(apparent) change is that one critical tx is taking much more time -
and other sessions are selecting the modified block.
--
Alberto Dell'Era
"the more you know, the faster you go"
--
//www.freelists.org/webpage/oracle-l
--
Alberto Dell'Era
"the more you know, the faster you go"
--
//www.freelists.org/webpage/oracle-l
Other related posts:
