讨论一下 block cleanout

显示全部楼层 · 2005-10-30 17:05:33

很多书上的提到，commit后的block cleanout，比如TOM 的expert one on one，但是，只要我们稍微留意就会发现：block cleanout并不止发生在commit后，commit之前也会发生。也就是说，当一个session在一个表上有未提交active transaction，另一个session的select语句查询时，同样会发生block cleanout，并产生redo，简单的autotrace可以验证。
现在我们从logfile dump对比一下。
未提交时select语句的cleanout：
REDO RECORD - Thread:1 RBA: 0x000022.00004e57.01b0 LEN: 0x0040 VLD: 0x01
SCN: 0x0000.001569ac SUBSCN:1 12/31/2011 22:12:17
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x010129dc OBJ:74487 SCN:0x0000.001567bd SEQ:1 OP:4.1 ENC:0 RBL:0
Block cleanout record, scn:0x0000.001569ac ver: 0x01 opt: 0x01, entries follow...
提交后select语句的cleanout：
REDO RECORD - Thread:1 RBA: 0x000022.00004ed0.0088 LEN: 0x0048 VLD: 0x01
SCN: 0x0000.00156c72 SUBSCN:1 12/31/2011 22:12:26
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x01012c78 OBJ:74487 SCN:0x0000.00156c3a SEQ:1 OP:4.1 ENC:0 RBL:0
Block cleanout record, scn:0x0000.00156c72 ver: 0x01 opt: 0x01, entries follow...
itli: 1flg: 2scn: 0x0000.00156c70
貌似commit前发生的cleanout只产生了log，却没有修改ITL，而log record的长度也稍短（64byte vs. 72byte）。
因此，active transation不仅会加大 consistent gets，同样会产生额外的redo。

千问 · 2005-10-30 17:05:33

We discussed non-committed cleanout before. See
http://www.itpub.net/thread-1353711-3-2.html
and I also referenced Jonathan Lewis's message in my msg #32 on the next page.
But there's no definitive conclusion. It seems this non-committed cleanout does occur, and is undocumented. I hope you open an SR with Oracle and don't forget to tell us what Oracle says. In the SR, you probably need to show the analysts statistics in v$sesstat (or v$sysstat), with or without block dumps. (If it's just block dump, he may refuse to answer your question.)
Yong Huang

千问 · 2005-10-30 17:05:33

Thanks, better understood. but I don't think GCS may server for such case, they're working on tech issues instead of our interest.

千问 · 2005-10-30 17:05:33

号称这个redo的影响是很少的，但我一直很奇怪，为什么Oracle要这样做。

千问 · 2005-10-30 17:05:33

> 号称这个redo的影响是很少的，
Where did you read this "号称"?
Yong Huang

千问 · 2005-10-30 17:05:33

Yong Huang 发表于 2012-1-9 22:14
> 号称这个redo的影响是很少的，
Where did you read this "号称"?
没有官方的说法而已