delayed logging block cleanout
- From: "Diego Cutrone" <diegocutrone@xxxxxxxxxxxx>
- To: "Oracle List" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 May 2004 17:05:20 -0700
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
