RE: delayed block cleanout (?) for active tx

  • From: "Shamsudeen, Riyaj" <RS2273@xxxxxxx>
  • To: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
  • Date: Thu, 21 Jun 2007 16:50:57 -0500

Hi Alberto

        It's been a while I did this research, so my memory may be
foggy, let me do slightly more research and get back to you. 

Thanks
Riyaj

-----Original Message-----
From: Alberto Dell'Era [mailto:alberto.dellera@xxxxxxxxx] 
Sent: Thursday, June 21, 2007 4:39 PM
To: Shamsudeen, Riyaj
Cc: Oracle-L
Subject: Re: delayed block cleanout (?) for active tx

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"
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


-- 
Alberto Dell'Era
"the more you know, the faster you go"
--
http://www.freelists.org/webpage/oracle-l


Other related posts: