崔华,网名 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
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),
……省略显示部分内容
“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%’;
NAME VALUE
——————– ——————–
_row_cr FALSE
然后我们再次以同样的方法重复之前测试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%’;
NAME VALUE
——————– ——————–
_row_cr TRUE
从如下查询结果中我们可以看到,表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 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
然后我们再次以同样的方法重复之前测试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后的数据,而不再存在以前的那种一致读的行为。
搜索 盖国强(Eygle) :eeygle,或者扫描下面二维码,备注:云和恩墨大讲堂,即可入群。每周与千人共享免费技术分享,与讲师在线讨论。
(OraNews)回复关键字获取 2016DTCC, 2016数据库大会PPT; DBALife,"DBA的一天"精品海报大图; 12cArch,“Oracle 12c体系结构”精品海报; DBA01,《Oracle DBA手记》第一本下载; YunHe,“云和恩墨大讲堂”案例文档下载;