[20210304]关于11g一致性读取的测试.txt

[20210304]关于11g一致性读取的测试.txt --//链接：http://blog.itpub.net/267265/viewspace-2760454/ => [20210301]延迟显示输出.txt --//Oracle将这种改动称为"RowCR Optimization"，Oracle简单的描述了什么是RowCR Optimization：A brief overview of this --//optimization is that we try to avoid rollbacks while constructing a CR block if the present block has no uncommitted --//changes.这里的avoid rollback，意味着在满足特定的条件时，Oracle就不做一致读了. --//这个提前条件就是索引要唯一. --//自己好奇补充一些测试一致性读取的情况。 1.环境: SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production CREATE OR REPLACE FUNCTION SCOTT.sleep (seconds IN NUMBER)    RETURN NUMBER AS BEGIN    sys.DBMS_LOCK.sleep (seconds);    RETURN seconds; END; / 2.建立测试环境： SCOTT@book> create table t as select rownum id1,rownum id2,lpad(rownum||'x',10,rownum||'x') vc from dual connect by level<=10; Table created. SCOTT@book> create unique index i_t_id1 on t(id1); Index created. SCOTT@book> create  index i_t_id2 on t(id2); Index created. --//分析略. 3.测试: --//session 1: SCOTT@book> select * from t where id1=4 and sleep(12)=12 union all select * from t where id2=4 and sleep(1)=12; --//session 2,以下命令最好事先输入,避免12秒内无法完成: SCOTT@book> update t set vc=upper(vc) where id1=4; 1 row updated. SCOTT@book> commit ; Commit complete. --//session 1: SCOTT@book> select * from t where id1=4 and sleep(12)=12 union all select * from t where id2=4 and sleep(12)=12;        ID1        ID2 VC ---------- ---------- --------------------          4          4 4X4X4X4X4X          4          4 4X4X4X4X4X --//你可以发现vc输出是大写.而按照我前面的测试应该第1行vc应该是小写，第2行输出的vc是大写。 4.继续测试： --//条件反过来查询呢？ --//session 1: SCOTT@book> select * from t where id2=3 and sleep(12)=12 union all select * from t where id1=3 and sleep(12)=12; --//session 2: SCOTT@book> update t set vc=upper(vc) where id1=3; 1 row updated. SCOTT@book> commit ; Commit complete. --//session 1: SCOTT@book> select * from t where id2=3 and sleep(12)=12 union all select * from t where id1=3 and sleep(12)=12;        ID1        ID2 VC ---------- ---------- --------------------          3          3 3x3x3x3x3x          3          3 3x3x3x3x3x --//再次查询： SCOTT@book> select * from t where id2=3 and sleep(1)=1 union all select * from t where id1=3 and sleep(1)=1;        ID1        ID2 VC ---------- ---------- --------------------          3          3 3X3X3X3X3X          3          3 3X3X3X3X3X --//11g这种改动可能会出现许多奇怪的情况，也许还可以测试更多的情况，不想做了。