Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: delayed block cleanout (?) for active tx

Re: delayed block cleanout (?) for active tx

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Thu, 21 Jun 2007 23:38:43 +0200
Message-ID: <4ef2fbf50706211438l60e7885fwec212e904f74563c@mail.gmail.com>


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_at_att.com> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] 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
Received on Thu Jun 21 2007 - 16:38:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US