delayed logging block cleanout

From: Diego Cutrone <diegocutrone_at_yahoo.com.ar>
Date: Wed, 26 May 2004 17:05:20 -0700
Message-ID: <000c01c4437e$50861000$4d8049c8@DC>

Hi List,
I was showing another DBA how delayed block clean out mechanism works when I found this....

Oracle 8.0.6.3.0
delayed_logging_block_cleanouts=TRUE (default)

created a simple table, inserted some records, commited. Then performed a clean out (analyzed the table), updated all rows and commited again

Table size around 7500 blocks
Buffer cache size 64000 blocks

SQL> update test_dbc set first=first+1;
350000 rows updated.

SQL> commit;
Commit complete.

(blockdump) Block header after the update and the commit.

Block header dump: rdba: 0x00419809
 Object id on Block? Y
 seg/obj: 0x16a66 csc: 0x698.b1acfb7c itc: 1 flg: - typ: 1 - DATA

     fsl: 0 fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01 0x0002.05f.0001454f 0x00822873.0b8f.29 ---- 48 fsc 0x0000.00000000

(Up to here everything was looking fine.....)

Then I ran a simple query from another session:

SQL> select sum(first) from test_dbc;

SUM(FIRST)

---

 6.125E+10

and I got:

 select a.sid, b.name, a.value
 from v$sesstat a , v$statname b
 where a.STATISTIC#=b.STATISTIC#
 and (b.name like '%clean%' or b.name like '%redo%')  and a.sid=&sid
 and a.value != 0
 order by 2

      SID NAME                                                                 VALUE
--------- ---------------------------------------------------------------- ---------
        9 cleanouts only - consistent read gets                                  872
        9 immediate (CR) block cleanout applications                             872
        9 redo entries                                                           872
        9 redo size                                                            52320

So I took another blockdump (Same blockdump taken before) after the query execution:

Block header dump: rdba: 0x00419809
 Object id on Block? Y
 seg/obj: 0x16a66 csc: 0x698.b1acfbfc itc: 1 flg: - typ: 1 - DATA

     fsl: 0 fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01 0x0002.05f.0001454f 0x00822873.0b8f.29 C--- 0 scn 0x0698.b1acfbcc

why did this block get cleaned out? . I just executed a query not a DML command. I then checked every table block and all of them got cleaned out by the query shown above. AFAIK in Oracle 7 and Oracle 8 version after executing a query this block should not have been cleaned out unless delayed_logging_block_cleanouts=FALSE

(I have also tested this in an Oracle 7 instance, and I got the same result)

Please shed some light on this.

Thanks
Diego.

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed May 26 2004 - 15:01:02 CDT