oracle 11g怎么读,深入解析：Oracle由11g而始的数据库一致读行为的改变

崔华，网名 dbsnake

Oracle ACE Director，ACOUG 核心专家

编辑手记：感谢崔华授权我们独家转载其精品文章，也欢迎大家向“Oracle”社区投稿。

我们都知道，在Oracle数据库中是“未commit的数据我们读不到，commit后的数据我们也不一定能马上读到”，这其中的后者当然是因为Oracle数据库中久负盛名的一致读行为的存在。

但从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，这也许是不恰当的。”

说了这么多背景，我们现在来看一个RowCR Optimization的实例：

这里用的Oracle数据库版本为11.2.0.1，我们在其中创建了如下的存储过程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

2cursor c1 is select * from emp where empno=7369;

3employee_rec emp%rowtype;

4begin

5open c1;

6dbms_lock.sleep(60);

7

8fetch c1 into employee_rec;

9while (c1%found) loop

10

11dbms_output.put_line(’employee id: ‘ || employee_rec.empno);

12dbms_output.put_line(’employee name: ‘ || employee_rec.ename);

13

14fetch c1 into employee_rec;

15end loop;

16close c1;

17end 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),

……省略显示部分内容

“DEPTNO” NUMBER(2,0),

CONSTRAINT “PK_EMP” PRIMARY KEY (“EMPNO”)

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

……省略显示部分内容

FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE “USERS”

现在表EMP中empno=7369的记录所对应的ename列的值是“SMITH”：

SQL> select empno,ename from emp where empno=7369;

EMPNO ENAME

—– ———-

7369 SMITH

我们现在开启两个Session，分别为Session 1和Session 2，在Session 1中执行上述存储过程p_demo_cr_read_change，同时在Session 2中利用Session 1的存储过程p_demo_cr_read_change从open c1到fetch之间间隔的那60秒缓冲时间对表EMP中empno=7369的记录做修改操作，即在这60秒缓冲时间内将表EMP中empno=7369的记录所对应的ename列的值从“SMITH”改为“CUIHUA”并commit，这样通过观察Session 1中存储过程p_demo_cr_read_change最后的输出结果即可验证RowCR Optimization的行为，具体来说就是如下这样：

1、Session 1中的最终输出结果如果是“SMITH”，则说明发生了常规的一致读，没有做RowCR Optimization；

2、Session 1中的最终输出结果如果是“CUIHUA”，则说明已经做了RowCR Optimization，没有做常规的一致读；

现在我们来实际测试一下，在Session 1中执行p_demo_cr_read_change：

Session 1：

SQL> set serveroutput on

SQL> exec p_demo_cr_read_change

……这里会等待60秒，然后才会显示输出结果

然后在60秒的缓冲时间段内去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，此时看到了如下输出结果：

Session 1：

SQL> exec p_demo_cr_read_change

employee id: 7369

employee name: CUIHUA

PL/SQL procedure successfully completed

上述Session 1的最终输出结果显示Oracle此时确实没有做常规的一致读，而是马上读到了commit后的数据，即此时已经发生了RowCR Optimization。

我们现在去Session 2中把表EMP中empno=7369的记录所对应的ename列的值恢复成原先的“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

然后我们再次以同样的方法重复之前测试RowCR Optimization的过程，Session 1的最终输出结果为如下所示：

Session 1：

SQL> exec p_demo_cr_read_change

employee id: 7369

employee name: SMITH

PL/SQL procedure successfully completed

上述Session 1的最终输出结果显示Oracle此时并没有做RowCR Optimization，即并没有马上读到commit后的数据，这说明当我们把列empno上的主键drop掉后(即drop掉empno上的唯一性索引后)，Oracle并没有做RowCR Optimization，而是做了常规的一致读。

我们现在去Session 2中把表EMP中empno=7369的记录所对应的ename列的值恢复成原先的“SMITH”并且在列empno上创建一个名为idx_emp_empno的非唯一性索引：

Session 2：

……这里省略将ename列的值恢复成原先的“SMITH”的过程

SQL> create index idx_emp_empno on emp(empno);

Index created

然后我们再次以同样的方法重复之前测试RowCR Optimization的过程，Session 1的最终输出结果为如下所示：

Session 1：

SQL> exec p_demo_cr_read_change

employee id: 7369

employee name: SMITH

PL/SQL procedure successfully completed

上述Session 1的最终输出结果显示Oracle此时也没有做RowCR Optimization，即并没有马上读到commit后的数据，这说明即使列empno上存在非唯一性索引，Oracle也没有做RowCR Optimization，而是做了常规的一致读。

我们现在去Session 2中把表EMP中empno=7369的记录所对应的ename列的值恢复成原先的“SMITH”并且在列empno上创建一个名为idx_uk_emp_empno的唯一性索引：

Session 2：

……这里省略将ename列的值恢复成原先的“SMITH”的过程

SQL> drop index idx_emp_empno;

Index dropped

SQL> create unique index idx_uk_emp_empno on emp(empno);

Index created

然后我们再次以同样的方法重复之前测试RowCR Optimization的过程，Session 1的最终输出结果为如下所示：

Session 1：

SQL> exec p_demo_cr_read_change

employee id: 7369

employee name: CUIHUA

PL/SQL procedure successfully completed

上述Session 1的最终输出结果显示Oracle此时没有做常规的一致读，而是马上读到了commit后的数据，即在列empno存在唯一性索引的情形下，Oracle选择做了RowCR Optimization。

我们现在去Session 2中把表EMP中empno=7369的记录所对应的ename列的值恢复成原先的“SMITH”并且将隐含参数“_row_cr”的值改为FALSE：

Session 2：

……这里省略将ename列的值恢复成原先的“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%’;

NAMEVALUE

——————– ——————–

_row_crFALSE

然后我们再次以同样的方法重复之前测试RowCR Optimization的过程，Session 1的最终输出结果为如下所示：

Session 1：

SQL> exec p_demo_cr_read_change

employee id: 7369

employee name: SMITH

PL/SQL procedure successfully completed

上述Session 1的最终输出结果显示即使列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.

但经过我们测试，上述这种说法是不严谨的：

测试过程为如下所示：

我们现在去Session 2中把表EMP中empno=7369的记录所对应的ename列的值恢复成原先的“SMITH”并且将隐含参数“_row_cr”的值改为TRUE：

Session 2：

……这里省略将ename列的值恢复成原先的“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%’;

NAMEVALUE

——————–——————–

_row_crTRUE

从如下查询结果中我们可以看到，表EMP的13条记录全部在datafile 4，block 151这个数据块中：

SQL> select empno, ename, dbms_rowid.rowid_relative_fno(rowid) || ‘_’ || dbms_rowid.rowid_block_number(rowid) location from emp;

EMPNO ENAMELOCATION

—– ———- ——————–

7369 SMITH 4_151

7499 ALLEN4_151

7521 WARD4_151

7566 JONES4_151

7654 MARTIN4_151

7698 BLAKE4_151

7782 CLARK4_151

7788 SCOTT4_151

7844 TURNER4_151

7876 ADAMS4_151

7900 JAMES4_151

7902 FORD4_151

7934 MILLER4_151

13 rows selected

然后我们再次以同样的方法重复之前测试RowCR Optimization的过程，只不过这一次在Session 2中做了如下的修改操作(即构造了datafile 4，block 151这个数据块存在未commit数据的情形)：

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，此时看到了如下输出结果：

Session 1：

SQL> exec p_demo_cr_read_change

employee id: 7369

employee name: CUIHUA

PL/SQL procedure successfully completed

上述Session 1的最终输出结果显示Oracle此时没有做常规的一致读，而是马上读到了commit后的数据，即在被访问的数据块存在未commit的数据的情形下也依然发生了RowCR Optimization。

最后，我们来总结一下，从上述测试过程我们可以得到如下结论：

在Oracle 11g以及后续的版本中，默认情况下(即隐含参数“_row_cr”的值为TRUE的情况下)，如果是通过唯一性索引去访问数据，则我们就可以马上读到commit后的数据，而不再存在以前的那种一致读的行为。如何加入"云和恩墨大讲堂"微信群