0

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

崔华 2016-03-03
243


崔华,网名 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 住。


如何加入"云和恩墨大讲堂"微信群

搜索 盖国强(Eygle)微信号:eeygle,或者扫描下面二维码,备注:云和恩墨大讲堂,即可入群。每周与千人共享免费技术分享,与讲师在线讨论。


近期文章

成就卓越:云和恩墨大讲堂期刊第三期

新年贺礼:云和恩墨大讲堂期刊第二期

删繁就简-云和恩墨的一道面试题解析

用SQL解一道数学题:Gauss和Poincare

新年贺礼:云和恩墨大讲堂期刊发行

2015 Oracle 十大热门文章精选

Oracle 12c ASM 防火防盗新特性揭秘

DBA入门之路:学习与进阶之经验谈

DBA入门之路:关于日常工作的建议


云和恩墨

数据驱动,成就未来。整合业界顶尖的技术与合作伙伴资源,围绕数据及相关领域,提供解决方案和专业服务。(业务联系电话: 01059003186-8019 )
业务架构
电子渠道(网络销售)分析系统、数据治理
IT基础架构
分布式存储解决方案 | zData一体机 | 容灾环境建设
数据架构
Oracle DB2 MySQL NoSQL
专项服务:架构/安全/容灾/优化/整合/升级/迁移
运维服务:运维服务  代维服务
人才培养:个人认证  企业内训
软件产品:SQL审核、监控、数据恢复
应用架构
应用软件和中间件:数据建模 | SQL审核和优化 | 中间件服务


「喜欢文章,快来给作者赞赏墨值吧」
文章转载自崔华,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

数据库资讯
最新 热门 更多
本月热门
近期活动
全部
暂无活动,敬请期待...
相关课程
全部