深入解析:Oracle由11g而始的数据库一致读行为的改变

崔华,网名 dbsnake

Oracle ACE Director,ACOUG 核心专家

我们都知道,在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后的数据,而不再存在以前的那种一致读的行为

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-05-23

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

MySQL中GTID的几个限制和解决方案

现在我看待一个技术,总是会换一种角度来看,在他能实现什么的基础上,我更喜欢看他不能做什么,为什么不能这么做。 比如MySQL GTID在5.6试水,...

5006
来自专栏python3

mysql-表的操作

数据库中的表也应该有不同的类型,表的类型不同,会对应mysql不同的存取机制,表类型又称为存储引擎

521
来自专栏向治洪

Java 读写大文本文件

如下的程序,将一个行数为fileLines的文本文件平均分为splitNum个小文本文件,其中换行符’r’是linux上的,windows的java换行符是’...

17510
来自专栏智能大石头

6,ORM组件XCode(撬动千万级数据)

有了前面的《动手》,基本上可以进行开发了。本篇我们来试试XCode的基本功功力如何,测试在单表一千万业务数据的环境下查询的速度,添删改等没什么可测试的。其实应...

1888
来自专栏java一日一条

MySQL 性能优化的最佳 20+ 条经验

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我 们程序员需要去关注的...

342
来自专栏java一日一条

MySQL 性能优化的最佳 20+ 条经验

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我 们程序员需要去关注的...

673
来自专栏乐沙弥的世界

db_block_checking与db_block_checksum

    db_block_checking与db_block_checksum两个参数都是对block进行检查,然而两者很容易混淆。事实上,两个参数中前者是对块...

723
来自专栏谢庆玲的专栏

MySQL 查询分析

本文主要由一个案例引发对 MySQL 性能问题的思考,主要讲述 MySQL 慢查询和 explain 工具这两个定位 MySQL 性能瓶颈的方法。

8221
来自专栏大数据挖掘DT机器学习

50多条实用mysql数据库优化建议

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 缺省情况下建立的索引是非群集索引,但有时它并不是最...

4666
来自专栏文渊之博

SSIS技巧--优化数据流缓存

问题     我们经常遇到一种情况,在SSMS中运行很慢的一个查询,当把查询转化成从源到目的数据库的SSIS数据流以后,需要花费几倍的时间!源和数据源都没有任何...

1849

扫码关注云+社区