[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這種改動可能會出現許多奇怪的情況，也許還可以測試更多的情況，不想做了。