[疑问]CR与索引的关系

ccsnmoracle

原帖由 just_dba 于 2010-6-28 22:41 发表
不一定是一致性读取,dml,truncate等语句,都会产生CR

我刚才truncate了一个表，但是没发现产生CR.

环境 Version 10.2.0.3.0

just_dba

truncate会修改段头块,一定会有CR的啊.你再测试一下

ccsnmoracle

我又试了一次，还是没发现。。。

用来测试的机器上不了网，所以不能把纪录贴出来。

Yong Huang

> 在索引存在的条件下，为什末不会产生呢？

Here's some interesting finding. I can reproduce it in RAC (mine is 10.2.0.4). I cannot reproduce it in single-instance, non-RAC databases (I tested in 10.2.0.4 and 10.2.0.3). In fact, the update with or without an index doesn't make any difference. Neither created a CR block for the *data* block in non-RAC (although one CR for the second BMB block and one CR for the segment header were created).

For others interested in this discussion, here's a step-by-step procedure to reproduce.

create table testcr (x int);
select header_file, header_block from dba_segments where segment_name = 'TESTCR' and owner = user;
--and I got  4 and 22179. Since tablespace is ASSM, I check 2 BMB blocks, header block, and the data block. The BMB and header blocks don't have to be checked but since just_dba claims he saw CR created for the segment header, I'd like to see that really can happen.
select block#, status from v$bh where file# = 4 and block# between 22177 and 22180 order by 1;
--should show 2 BMB blocks and 1 header block, all with xcur status

insert into testcr values (1);
select block#, status from v$bh where file# = 4 and block# between 22177 and 22180 order by 1;
--should show one more row for data block in xcur status

update testcr set x = 1;
select block#, status from v$bh where file# = 4 and block# between 22177 and 22180 order by 1;
--should show one CR created for the data block in RAC, but no new block in non-RAC

create index testcr_i on testcr (x);
update testcr set x = 1;
select block#, status from v$bh where file# = 4 and block# between 22177 and 22180 order by 1;
--should show one more CR created (RAC only)

update testcr set x = 1;
select block#, status from v$bh where file# = 4 and block# between 22177 and 22180 order by 1;
--should show no new CR created

The last step showing no new CR created is what ccsnmoracle asks. Please let me know if your DB is RAC. I suggest ccsnmoracle opens an SR with Oracle to find a good answer.

Yong Huang

[ 本帖最后由 Yong Huang 于 2010-6-28 13:03 编辑 ]

just_dba

估计你有一个地方没注意,v$bh里边的objd=dba_objects.data_object_id,truncate之后,data_object_ID会变化,产生一个新的,你用这个去查查

ccsnmoracle

>>估计你有一个地方没注意,v$bh里边的objd=dba_objects.data_object_id,truncate之后,data_object_ID会变化,产生一个新的,你用这个去查查

好的。我再去看看

>>The last step showing no new CR created is what ccsnmoracle asks. Please let me know if your DB is RAC. I suggest ccsnmoracle opens an SR with Oracle to find a good answer.

我只是在Single Instance的环境下，测试过。我再找一个RAC环境看看结果。
这种个人学习目的的SR,我们公司是不容许的。比较无奈。。。

Yong Huang

> 我只是在Single Instance的环境下，测试过。

So yours is reproduced in a single-instance database. Mine is just the opposite. I don't know why.

> 这种个人学习目的的SR,我们公司是不容许的

What kind of company is that? If the employer discourages self-motivating employees from self-study, I don't see how the company can succeed in a competitive world. The boss needs more education in the new world.

Yong Huang

sqysl

1、我在测试环境下，即使再表上建立了索引，也是出现了状态为CR的表块（我认为楼主的测试是有漏洞的，在更新没索引的表后，应该刷新一下BUFFERS，然后再建立索引，查看BUFFER的状态，这样应该更严格些）；
2、我觉得出现状态为CR的表块，并不意味着一定存在一致性读，只是说明这个块可用来进行一致性读而已；
3、V$BH的status的值CR，说明这个块不是数据块的当前版本，而是数据块的一个前影像或克隆，可以用于进行一致性视图的构建；而值XCUR，则说明该块为数据块的当前版本，也就是最新版本，在有DML操作的系统上，BUFFER中存在CR的块是很正常的，有时一个数据块可能存在多个版本，也就是多个状态为CR的BUFFER，这也是MVCC的特色，当然，这个版本数应该是有限制的，目的是为了提高一致性读的性能。

欢迎大家一起探讨。