崔華，網(wǎng)名 dbsnake
Oracle ACE Director，ACOUG 核心專家
編輯手記：感謝崔華授權(quán)我們獨家轉(zhuǎn)載其精品文章，也歡迎大家向“Oracle”社區(qū)投稿。
我們都知道，在Oracle數(shù)據(jù)庫中是“未commit的數(shù)據(jù)我們讀不到，commit后的數(shù)據(jù)我們也不一定能馬上讀到”，這其中的后者當然是因為Oracle數(shù)據(jù)庫中久負盛名的一致讀行為的存在。
但從Oracle 11g開始，Oracle更改了在某些特定條件一致讀的行為，這使得一些看起來不合常理的行為在Oracle 11g以及后續(xù)的版本中得以出現(xiàn)，即在Oracle 11g以及后續(xù)的版本中，當滿足一定的條件時，我們就可以馬上讀到commit后的數(shù)據(jù)，而不再存在以前的那種一致讀的行為。
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就不做一致讀了。
RowCR Optimization通過隱含參數(shù)“_row_cr”來控制，但遺憾的是，Oracle在11g及其后續(xù)的版本中將這個參數(shù)的默認值改成了TRUE，這意味著上述這種“在滿足特定的條件時，Oracle就不做一致讀”的行為在Oracle 11g及其后續(xù)的版本中在默認情況下就已經(jīng)被開啟了，這也許有些激進。國內(nèi)的某銀行在升級到Oracle 11g后就出現(xiàn)了一致讀的問題，在這次的CAB技術(shù)峰會上，Oracle負責(zé)高可用性研發(fā)的VP Wei Hu承認：“我們在默認情況下開啟了RowCR Optimization，這也許是不恰當?shù)摹?/span>”
說了這么多背景，我們現(xiàn)在來看一個RowCR Optimization的實例：
這里用的Oracle數(shù)據(jù)庫版本為11.2.0.1，我們在其中創(chuàng)建了如下的存儲過程p_demo_cr_read_change：
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> create or replace procedure p_demo_cr_read_change is
2 cursor c1 is select * from emp where empno=7369;
3 employee_rec emp%rowtype;
4 begin
5 open c1;
6 dbms_lock.sleep(60);
7
8 fetch c1 into employee_rec;
9 while (c1%found) loop
10
11 dbms_output.put_line(’employee id: ‘ || employee_rec.empno);
12 dbms_output.put_line(’employee name: ‘ || employee_rec.ename);
13
14 fetch c1 into employee_rec;
15 end loop;
16 close c1;
17 end p_demo_cr_read_change;
18 /
Procedure created
上述存儲過程針對的是SCOTT用戶下的表EMP，在列EMPNO上存在一個名為PK_EMP的主鍵：
SQL> select dbms_metadata.get_ddl(‘TABLE’,’EMP’,’SCOTT’) from dual;
CREATE TABLE “SCOTT”.”EMP”
( “EMPNO” NUMBER(4,0),
“ENAME” VARCHAR2(10),
……省略顯示部分內(nèi)容
“DEPTNO” NUMBER(2,0),
CONSTRAINT “PK_EMP” PRIMARY KEY (“EMPNO”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
……省略顯示部分內(nèi)容
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS”
現(xiàn)在表EMP中empno=7369的記錄所對應(yīng)的ename列的值是“SMITH”：
SQL> select empno,ename from emp where empno=7369;
EMPNO ENAME
—– ———-
7369 SMITH
我們現(xiàn)在開啟兩個Session，分別為Session 1和Session 2，在Session 1中執(zhí)行上述存儲過程p_demo_cr_read_change，同時在Session 2中利用Session 1的存儲過程p_demo_cr_read_change從open c1到fetch之間間隔的那60秒緩沖時間對表EMP中empno=7369的記錄做修改操作，即在這60秒緩沖時間內(nèi)將表EMP中empno=7369的記錄所對應(yīng)的ename列的值從“SMITH”改為“CUIHUA”并commit，這樣通過觀察Session 1中存儲過程p_demo_cr_read_change最后的輸出結(jié)果即可驗證RowCR Optimization的行為，具體來說就是如下這樣：
1、Session 1中的最終輸出結(jié)果如果是“SMITH”，則說明發(fā)生了常規(guī)的一致讀，沒有做RowCR Optimization；
2、Session 1中的最終輸出結(jié)果如果是“CUIHUA”，則說明已經(jīng)做了RowCR Optimization，沒有做常規(guī)的一致讀；
現(xiàn)在我們來實際測試一下，在Session 1中執(zhí)行p_demo_cr_read_change：
Session 1：
SQL> set serveroutput on
SQL> exec p_demo_cr_read_change
……這里會等待60秒，然后才會顯示輸出結(jié)果
然后在60秒的緩沖時間段內(nèi)去Session 2中做如下修改操作：
Session 2：
SQL> update emp set ename=’CUIHUA’ where empno=7369;
1 row updated
SQL> commit;
Commit complete
SQL> select empno,ename from emp where empno=7369;
EMPNO ENAME
—– ———-
7369 CUIHUA
等過了60秒的緩沖時間后，我們再回到Session 1，此時看到了如下輸出結(jié)果：
Session 1：
SQL> exec p_demo_cr_read_change
employee id: 7369
employee name: CUIHUA
PL/SQL procedure successfully completed
上述Session 1的最終輸出結(jié)果顯示Oracle此時確實沒有做常規(guī)的一致讀，而是馬上讀到了commit后的數(shù)據(jù)，即此時已經(jīng)發(fā)生了RowCR Optimization。
我們現(xiàn)在去Session 2中把表EMP中empno=7369的記錄所對應(yīng)的ename列的值恢復(fù)成原先的“SMITH”并且drop掉表EMP上的主鍵PK_EMP：
Session 2：
SQL> update emp set ename=’SMITH’ where empno=7369;
1 row updated
SQL> commit;
Commit complete
SQL> select empno,ename from emp where empno=7369;
EMPNO ENAME
—– ———-
7369 SMITH
SQL> alter table emp drop constraint pk_emp;
Table altered
然后我們再次以同樣的方法重復(fù)之前測試RowCR Optimization的過程， Session 1的最終輸出結(jié)果為如下所示：
Session 1：
SQL> exec p_demo_cr_read_change
employee id: 7369
employee name: SMITH
PL/SQL procedure successfully completed
上述Session 1的最終輸出結(jié)果顯示Oracle此時并沒有做RowCR Optimization，即并沒有馬上讀到commit后的數(shù)據(jù)，這說明當我們把列empno上的主鍵drop掉后（即drop掉empno上的唯一性索引后），Oracle并沒有做RowCR Optimization，而是做了常規(guī)的一致讀。
我們現(xiàn)在去Session 2中把表EMP中empno=7369的記錄所對應(yīng)的ename列的值恢復(fù)成原先的“SMITH”并且在列empno上創(chuàng)建一個名為idx_emp_empno的非唯一性索引：
Session 2：
……這里省略將ename列的值恢復(fù)成原先的“SMITH”的過程
SQL> create index idx_emp_empno on emp(empno);
Index created
然后我們再次以同樣的方法重復(fù)之前測試RowCR Optimization的過程， Session 1的最終輸出結(jié)果為如下所示：
Session 1：
SQL> exec p_demo_cr_read_change
employee id: 7369
employee name: SMITH
PL/SQL procedure successfully completed
上述Session 1的最終輸出結(jié)果顯示Oracle此時也沒有做RowCR Optimization，即并沒有馬上讀到commit后的數(shù)據(jù)，這說明即使列empno上存在非唯一性索引，Oracle也沒有做RowCR Optimization，而是做了常規(guī)的一致讀。
我們現(xiàn)在去Session 2中把表EMP中empno=7369的記錄所對應(yīng)的ename列的值恢復(fù)成原先的“SMITH”并且在列empno上創(chuàng)建一個名為idx_uk_emp_empno的唯一性索引：
Session 2：
……這里省略將ename列的值恢復(fù)成原先的“SMITH”的過程
SQL> drop index idx_emp_empno;
Index dropped
SQL> create unique index idx_uk_emp_empno on emp(empno);
Index created
然后我們再次以同樣的方法重復(fù)之前測試RowCR Optimization的過程， Session 1的最終輸出結(jié)果為如下所示：
Session 1：
SQL> exec p_demo_cr_read_change
employee id: 7369
employee name: CUIHUA
PL/SQL procedure successfully completed
上述Session 1的最終輸出結(jié)果顯示Oracle此時沒有做常規(guī)的一致讀，而是馬上讀到了commit后的數(shù)據(jù)，即在列empno存在唯一性索引的情形下，Oracle選擇做了RowCR Optimization。
我們現(xiàn)在去Session 2中把表EMP中empno=7369的記錄所對應(yīng)的ename列的值恢復(fù)成原先的“SMITH”并且將隱含參數(shù)“_row_cr”的值改為FALSE：
Session 2：
……這里省略將ename列的值恢復(fù)成原先的“SMITH”的過程
SQL> alter system set “_row_cr” = false scope=both;
System altered
SQL> select name,value from sys.all_parameters where name like ‘_row_cr%’;
NAME VALUE
——————– ——————–
_row_cr FALSE
然后我們再次以同樣的方法重復(fù)之前測試RowCR Optimization的過程， Session 1的最終輸出結(jié)果為如下所示：
Session 1：
SQL> exec p_demo_cr_read_change
employee id: 7369
employee name: SMITH
PL/SQL procedure successfully completed
上述Session 1的最終輸出結(jié)果顯示即使列empno上存在唯一性索引，Oracle此時也沒有做RowCR Optimization，即并沒有馬上讀到commit后的數(shù)據(jù)，這說明隱含參數(shù)“_row_cr”確實能控制RowCR Optimization的開啟與否。
Oracle在描述RowCR Optimization時曾經(jīng)提到其生效的前提條件為：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.
但經(jīng)過我們測試，上述這種說法是不嚴謹?shù)模?/span>
測試過程為如下所示：
我們現(xiàn)在去Session 2中把表EMP中empno=7369的記錄所對應(yīng)的ename列的值恢復(fù)成原先的“SMITH”并且將隱含參數(shù)“_row_cr”的值改為TRUE：
Session 2：
……這里省略將ename列的值恢復(fù)成原先的“SMITH”的過程
SQL> alter system set “_row_cr” = true scope=both;
System altered
SQL> select name,value from sys.all_parameters where name like ‘_row_cr%’;
NAME VALUE
——————– ——————–
_row_cr TRUE
從如下查詢結(jié)果中我們可以看到，表EMP的13條記錄全部在datafile 4，block 151這個數(shù)據(jù)塊中：
SQL> select empno, ename, dbms_rowid.rowid_relative_fno(rowid) || ‘_’ || dbms_rowid.rowid_block_number(rowid) location from emp;
EMPNO ENAME LOCATION
—– ———- ——————–
7369 SMITH 4_151
7499 ALLEN 4_151
7521 WARD 4_151
7566 JONES 4_151
7654 MARTIN 4_151
7698 BLAKE 4_151
7782 CLARK 4_151
7788 SCOTT 4_151
7844 TURNER 4_151
7876 ADAMS 4_151
7900 JAMES 4_151
7902 FORD 4_151
7934 MILLER 4_151
13 rows selected
然后我們再次以同樣的方法重復(fù)之前測試RowCR Optimization的過程，只不過這一次在Session 2中做了如下的修改操作（即構(gòu)造了datafile 4，block 151這個數(shù)據(jù)塊存在未commit數(shù)據(jù)的情形）：
Session 2：
SQL> update emp set ename=’CUIHUA’ where empno=7369;
1 row updated
SQL> commit;
Commit complete
SQL> select empno,ename from emp where empno=7369;
EMPNO ENAME
——- ———-
7369 CUIHUA
SQL> update emp set ename=’CUIHUA1′ where empno=7499;
1 row updated
等過了60秒的緩沖時間后，我們再回到Session 1，此時看到了如下輸出結(jié)果：
Session 1：
SQL> exec p_demo_cr_read_change
employee id: 7369
employee name: CUIHUA
PL/SQL procedure successfully completed
上述Session 1的最終輸出結(jié)果顯示Oracle此時沒有做常規(guī)的一致讀，而是馬上讀到了commit后的數(shù)據(jù)，即在被訪問的數(shù)據(jù)塊存在未commit的數(shù)據(jù)的情形下也依然發(fā)生了RowCR Optimization。
最后，我們來總結(jié)一下，從上述測試過程我們可以得到如下結(jié)論：
在Oracle 11g以及后續(xù)的版本中，默認情況下（即隱含參數(shù)“_row_cr”的值為TRUE的情況下），如果是通過唯一性索引去訪問數(shù)據(jù)，則我們就可以馬上讀到commit后的數(shù)據(jù)，而不再存在以前的那種一致讀的行為。
