[20130902]Oracle 11G資料庫的一致讀性讀取行為的改變.txt

[20130902]Oracle 11G資料庫的一致讀性讀取行為的改變.txt



昨天看了崔華的帖子，blog中給出一個例子，例子如下：
create or replace procedure p_demo_cr_read_change is
  cursor c1 is select * from emp where empno=7369;
  employee_rec emp%rowtype;
begin
  open c1;
  dbms_lock.sleep(60);
                                                                    
  fetch c1 into employee_rec;
  while (c1%found) loop
                                                                   
     dbms_output.put_line('employee id: ' || employee_rec.empno);
     dbms_output.put_line('employee name: ' || employee_rec.ename);
                                                                   
    fetch c1 into employee_rec;
  end loop;
  close c1;
end p_demo_cr_read_change;
/

按照以前的理解，如果在open 游標後，結果已經確定，即使別的回話修改了 empno=7369的ename值，顯示的結果

 select * from emp where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

    但從Oracle 11g開始，Oracle更改了在某些特定條件一致讀的行為，這使得一些看起來不合常理的行為在Oracle 11g以及後續的版本
中得以出現，即在Oracle 11g以及後續的版本中，當滿足一定的條件時，我們就可以馬上讀到commit後的資料，而不再存在以前的那種
一致讀的行為。

    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透過隱含引數"_row_cr"來控制，但遺憾的是，Oracle在11g及其後續的版本中將這個引數的預設值改成了TRUE，
這意味著上述這種"在滿足特定的條件時，Oracle就不做一致讀"的行為在Oracle 11g及其後續的版本中在預設情況下就已經被開啟了，
這也許有些激進。國內的某銀行在升級到Oracle 11g後就出現了一致讀的問題，在這次的CAB技術峰會上，Oracle負責高可用性研發的VP
Wei Hu承認："我們在預設情況下開啟了RowCR Optimization，這也許是不恰當的。"

    這些可能導致與早期的理解不一致，重複他的例子來說明問題：

1. 測試環境:

SCOTT@test> @ver

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--建立過程p_demo_cr_read_change，忽略,執行指令碼在上面。

SCOTT@test> select empno,ename from emp where empno=7369;

     EMPNO ENAME

---------- ----------

      7369 SMITH

2.開始測試：
--開啟回話1：
set serveroutput on
exec p_demo_cr_read_change

--開啟回話2：
update emp set ename='lfree' where empno=7369;
commit ;

--回到回話1：
employee id: 7369
employee name: lfree

PL/SQL procedure successfully completed.

可以發現回話1沒有做常規的一致讀，而是馬上讀到了commit後的資料，即此時已經發生了RowCR Optimization。


3.恢復原值，並且刪除emp表的主鍵PK_EMP：
--開啟回話2：
update emp set ename='SMITH' where empno=7369;
commit ;
alter table emp drop constraint pk_emp;
select empno,ename from emp where empno=7369;
     EMPNO ENAME
---------- ----------
      7369 SMITH

--然後重複測試:
--開啟回話1：
set serveroutput on
exec p_demo_cr_read_change

--開啟回話2：
update emp set ename='lfree' where empno=7369;
commit ;

--回到回話1：
employee id: 7369
employee name: SMITH

PL/SQL procedure successfully completed.

--這個結果就是正常的一致性讀取。此時並沒有做RowCR Optimization，即並沒有馬上讀到commit後的資料，這說明當我們把
--列empno上的主鍵drop掉後（即drop掉empno上的唯一性索引後），Oracle並沒有做RowCR Optimization，而是做了常規的一致讀。

4.在列empno上建立一個名為i_emp_empno的非唯一性索引，還原原值：
update emp set ename='SMITH' where empno=7369;
commit ;
create  index i_emp_empno on emp(empno);
--索引為不唯一索引。

select empno,ename from emp where empno=7369;

     EMPNO ENAME

---------- ----------

      7369 SMITH

--然後重複測試:
--回到回話1：
employee id: 7369
employee name: SMITH

PL/SQL procedure successfully completed.

--同樣也沒有做RowCR Optimization，說明如果索引非唯一，Oracle也沒有做RowCR Optimization，而是做了常規的一致讀。

5.在列empno上建立一個名為i_emp_empno的唯一性索引，還原原值：
update emp set ename='SMITH' where empno=7369;
commit ;
drop index i_emp_empno ;
create  unique index i_emp_empno on emp(empno);

select empno,ename from emp where empno=7369;

     EMPNO ENAME

---------- ----------

      7369 SMITH

--然後重複測試:
--回到回話1：
employee id: 7369
employee name: lfree

PL/SQL procedure successfully completed.

--列empno存在唯一性索引的情形下，Oracle選擇做了RowCR Optimization。

6.還原原值，並且修改系統隱含引數"_row_cr"=false：
update emp set ename='SMITH' where empno=7369;
commit ;
alter system set "_row_cr" = false scope=memory;

--然後重複測試:
--回到回話1：
employee id: 7369
employee name: SMITH

PL/SQL procedure successfully completed.

--可以即使列empno上存在唯一性索引，Oracle此時也沒有做RowCR Optimization，即並沒有馬上讀到commit後的資料，這說明隱含引數
--"_row_cr"確實能控制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.

7.還原原值，並且修改系統隱含引數"_row_cr"=true：
update emp set ename='SMITH' where empno=7369;
commit ;
alter system set "_row_cr" = true scope=memory;

--開啟回話1：
set serveroutput on
exec p_demo_cr_read_change

--開啟回話2：
update emp set ename='lfree' where empno=7369;
commit ;
update emp set ename='lfree1' where empno=7369;

--回到回話1：
employee id: 7369
employee name: SMITH

PL/SQL procedure successfully completed.

--顯示Oracle此時做了常規的一致讀，即在被訪問的資料塊存在未commit的資料的情形下不會發生RowCR Optimization。
--注：我這裡測試跟作者不一樣，估計版本問題11.2.0.3修復了這個錯誤，作者的版本是11.2.0.1.

8.還原原值,繼續測試：我自己補充的
update emp set ename='SMITH' where empno=7369;
commit ;

--開啟回話1：
set serveroutput on
exec p_demo_cr_read_change

--開啟回話2：修改empno=7369為8001
update emp set empno =8001 where empno=7369;
commit ;

--回到回話1：
employee id: 7369
employee name: SMITH

PL/SQL procedure successfully completed.

--顯示Oracle此時做了常規的一致讀。

    最後，我們來總結一下，從上述測試過程我們可以得到如下結論：

    在Oracle 11g以及後續的版本中，預設情況下（即隱含引數"_row_cr"的值為TRUE的情況下），如果是透過唯一性索引去訪問資料，
則我們就可以馬上讀到commit後的資料，而不再存在以前的那種一致讀的行為。

     補充1點：我個人認為是否會出現一些“錯誤”還是有待觀察，畢竟這種方式與原來的不同。