深入内核:Oracle数据库里SELECT操作Hang解析

崔华,网名 dbsnake

Oracle ACE Director,ACOUG 核心专家

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

我们都知道在 Oracle 数据库里是“读不阻塞写,写不阻塞读”,那么是否可以认为在正常情况下,select 操作是怎样都能执行,始终不会被 hang 住的呢?注意这里提到的是正常情况下,不包括那些由于 latch 被 hold 住、或者 bug 等相关异常导致的 select 操作 hang 住的情况。

答案是:不可以这样认为的。

我们来举一个反例。

首先我们来分析一下在 sql 硬解析时在相关表对象上 library cache lock 的持有情况。这里我用到了10049事件,用10049事件,最重要的就是要知道如何设置它所对应的 level 值。

10049的level值可能会有如下一些组合:

这里因为我要跟踪 sql 硬解析时相关表对象的 library cache lock 的持有情况,所以这里level 值取0x0210=0x0200|0x0010,即这里 level 值取528。

SQL> select to_number(‘210′,’XXXX’)

from dual;

先在11.2.0.1里使用一下10049事件:

C:\Documents and Settings\cuihua>sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on 星期三 6月 27 21:39:37 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. SQL> conn / as sysdba; 已连接。 SQL> oradebug setmypid 已处理的语句 SQL> oradebug event 10049 trace name context forever,level 528 已处理的语句 SQL> select count(*) from scott.emp; COUNT(*) ———- 14 SQL> oradebug tracefile_name c:\app\cuihua\diag\rdbms\cuihua112\ cuihua112\trace\cuihua112_ora_2292.trc

这个TRACE文件没有任何内容,看起来似乎是10049事件对11gR2无效或者 Oracle 改变了10049事件在11gR2中的 level 的定义(这个我不确定)。

我们换一个10gR2的版本:

SQL> select * from v$version;

SQL> oradebug setmypid 已处理的语句 SQL> oradebug event 10049 trace name context forever,level 528 已处理的语句 SQL> select count(*) from scott.emp; COUNT(*) ———- 13 SQL> oradebug tracefile_name d:\oracle\admin\cuihua\udump\cuihua_ora_5012.trc

从上述 trace 文件(d:\oracle\admin\cuihua\udump\cuihua_ora_5012.trc)中从前到后可以看到如下内容:

即针对上述 cursor 是以 NULL 模式持有 library cache lock,

针对表 scott.emp 是以 share 模式持有 library cache lock。

也就是说,只要我事先以 exclusive 模式在表 scott.emp上持有 library cache lock,那么后续的以硬解析方式执行的针对该表的所有sql(包括 select 语句)都将被 hang 住。

现在我们来测一下对一个表增加一个主键时的 library cache lock 的持有情况。

SQL> create table t2 as select * from emp; Table created SQL> select count(*) from t2; COUNT(*) ———- 13 SQL> conn / as sysdba; 已连接。 SQL> oradebug setmypid 已处理的语句 SQL> oradebug event 10049 trace name context forever,level 528 已处理的语句 SQL> alter table scott.t2 add constraint PK_T2 primary key (EMPNO); 表已更改。 SQL> oradebug tracefile_name d:\oracle\admin\cuihua\udump\cuihua_ora_6120.trc

从这个trace文件(d:\oracle\admin\cuihua\udump\cuihua_ora_6120.trc)中我们可以看出对表t2的 library cache lock 的先后持有模式为:

即大部分时间 library cache lock 的持有模式都是N,只有在一头一尾的时候才是X。

但请注意这种情况下 select 操作是会被hang住的。

因为一开头的X是 kglget,结尾才 kgllkdl(kgllkdl大致是 kgl lock delete 的意思,表示释放相应的 library cache lock),并且它们的 KGL Lock addr 相同:

这也就意味着在添加主键的整个过程中,Oracle始终会以 exclusive 模式在表 scott.t2 上持有 library cache lock,直到最后主键添加完毕了才释放。

所以在 win32上的10.2.0.1中,在添加主键的过程中会一直阻塞查询(select)操作。

我们来测一下,同时开3个session。

Session 1:

SQL> create table t3(id number); Table created SQL> declare 2 i number; 3 begin 4 for i in 1..3000000 loop 5 insert into t3 values (i); 6 end loop i; 7 commit; 8 end; 9 / PL/SQL procedure successfully completed

Session 2:

SQL> select * from v$mystat where rownum<2;

在 session 1中开始执行添加主键操作:

Session 1: SQL> alter table scott.t3 add constraint PK_T3 primary key (id); ……开始执行

转到 session 2执行查询操作:

Session 2:

SQL> select * from t3 where rownum<10; ……这里 hang 住了

转到 session 3并执行对 session2的等待事件的查询:

Session 3:

SQL> select t.event,t.state,t.seconds_in_wait from v$session t where sid=138;

从中可以看到 session 2在等待 library cache lock,同时它的STATE为waiting,SECONDS_IN_WAIT的值在递增。

这就验证了我们的结论:在 win32上的10.2.0.1中,在对表增加主键的过程中会一直阻塞对这个表的查询(select)操作。

现在我们再问一个问题:是不是所有对表的DDL操作,在DDL操作的执行过程中都会阻塞对这个表的select操作?

答案是:不是这样的。

我们来举一个反例。

现在我们来测一下对表 drop一个column 时 library cache lock 的持有情况:

SQL> desc t1;

SQL> select count(*) from t1;

同时开两个session。

在session 1中打开10049事件后drop表t1的列object_type:

Session 1:

SQL> conn / as sysdba; 已连接。 SQL> oradebug setmypid 已处理的语句 SQL> oradebug event 10049 trace name context forever,level 528 已处理的语句 SQL> alter table scott.t1 drop column OBJECT_TYPE; 表已更改。 SQL> oradebug tracefile_name d:\oracle\admin\cuihua\udump\ cuihua_ora_5020.trc

session 2在 session 1执行 drop column 操作的同时查询表t1,结果是 select 操作并没有被 hang 住,且能看到正在被 drop 的列 object_type:

Session 2:

SQL> select owner,object_name,object_type

from t1

where rownum<10;

从 session 1所产生的 trace 文件

(d:\oracle\admin\cuihua\udump\cuihua_ora_5020.trc)中我们可以看出对表t1的 library cache lock 的先后持有模式为:

即大部分时间对表 scott.t1 的 library cache lock 的持有模式都是S,最后才是X,所以这就可以解释为什么在对表 scott.t1 执行 drop column 操作的时候对它的select语句能够同时执行。

从 trace 文件来看,drop column 并不是不会阻塞 select 操作,只是阻塞的时间点要恰好是Oracle以X模式持有library cache lock时。

最后我们来测一下对一个表增加一个 unique constraint时library cache lock的持有情况

SQL> conn / as sysdba; 已连接。 SQL> oradebug setmypid 已处理的语句 SQL> oradebug event 10049 trace name context forever, level 528 已处理的语句 SQL> alter table scott.t2 add constraint UK_T2_EMPNO unique (EMPNO, ENAME); 表已更改。 SQL> oradebug tracefile_name d:\oracle\admin\cuihua\udump\cuihua_ora_5240.trc

从这个trace文件中我们可以看出对表 scott.t2 的 library cache lock 的先后持有模式为:

即大部分时间都是N,一头一尾才是X,这个和添加主键操作一样,在此不再赘述。

结论:不要随便在生产环境对大表执行DDL操作(如添加唯一性约束等),可能会导致针对这个表的所有 sql(包括select操作)在执行DDL操作的时间段都 hang 住。

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

原文发表时间:2016-03-03

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

基于 dbms_redefinition 在线重定义表

      Oracle 支持在线重定义表,也就是说我们可以在修改表结构(DDL)的同时进行相关的DQL、DML操作,使得前端的DML根本感觉不到表结构实际上已...

712
来自专栏Vamei实验室

被解放的姜戈02 庄园疑云

上一回说到,姜戈的江湖初体验:如何架设服务器,如何回复http请求,如何创建App。这一回,我们要走入糖果庄园。 数据库是一所大庄园,藏着各种宝贝。一个没有数据...

1845
来自专栏乐沙弥的世界

使用FREDATED引擎实现跨实例访问

    跨数据库服务器,跨实例访问是比较常见的一种访问方式,在Oracle中可以通过DB LINK的方式来实现。对于MySQL而言,有一个FEDERATED存储...

621
来自专栏杂烩

java visualvm监控远程服务 原

1、首先确保hostname绑定的不是localhost或者127.0.0.1、0.0.0.0,否则报

834
来自专栏cs

ASP.NET 数据库访问

新建数据,采用sql server数据库 use dflx; create table person --建立表 ( name char(12), ...

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

浅谈exp/imp(上) (r5笔记第81天)

作为DBA,经常需要在不同数据库环境间做数据的导入导出,exp/imp就是这样的轻便快捷的客户端工具,可以很方便的在不同数据库之间转移数据对象,即使数据库位于不...

2568
来自专栏网站那些事

利用 Zipkin 追踪 Mysql 数据库调用链

在前面:微服务调用链追踪中心搭建 一文中我们利用 Zipkin 搭建了一个微服务调用链的追踪中心,并且模拟了微服务调用的实验场景。利用 Zipkin 的库 Br...

48613
来自专栏乐沙弥的世界

ORA-00054 故障处理一例

最近index job出现失败,于是尝试手动执行试试。收到了ORA-00054的错误消息。

993
来自专栏Jerry的SAP技术分享

编程面试题:编写一个会造成数据库死锁的应用

相信对于"开发一个会产生死锁的Java应用”这类需求,大家都能顺利完成。但是如果题目要求得更具体一些,要求这个死锁发生在数据库层面,应该怎样完成呢?

751
来自专栏C/C++基础

MySQL错误:ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY

MySQL版本:Ver 14.14 Distrib 5.1.61, for redhat-linux-gnu (x86_64)

723

扫描关注云+社区