深入内核: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 条评论
登录 后参与评论

相关文章

来自专栏解Bug之路

MySql之自动同步表结构

在开发过程中,由于频繁的修改数据库的字段,导致rd和qa环境的数据库表经常不一致。 而由于这些修改数据库的操作可能由多个rd操作,很难一次性收集全。人手工去和...

551
来自专栏james大数据架构

微软官方提供的用于监控MS SQL Server运行状况的工具及SQL语句

Microsoft SQL Server 2005 提供了一些工具来监控数据库。方法之一是动态管理视图。动态管理视图 (DMV) 和动态管理函数 (DMF) 返...

2337
来自专栏MYSQL轻松学

MySQL 8.0.11 (2018-04-19, General Availability)

仅支持通过使用 in-place 方式从 MySQL 5.7 升级到 MySQL 8.0 升级; 不支持从 MySQL 8.0 降级到 MySQL 5....

872
来自专栏梅海峰的专栏

可重复读事务隔离级别之 django 解读

本文尝试结合 django 解释应用开发中并发访问数据库可能会遇到的可重复读引起的问题,希望能帮助大家在开发过程中有效避免类似问题。

2650
来自专栏乐沙弥的世界

MyCAT全局表描述及示例

1141
来自专栏吴生的专栏

MySQL 慢查询日志

MySQL有一种日志,叫做慢查询日志,主要就是用来记录一些耗时的查询操 作。通过这个日志我们就可以分析出哪些的操作是影响性能的,我们需要对其 进行一些优化措施。

2371
来自专栏散尽浮华

Mysql之binlog日志说明及利用binlog日志恢复数据操作记录

众所周知,binlog日志对于mysql数据库来说是十分重要的。在数据丢失的紧急情况下,我们往往会想到用binlog日志功能进行数据恢复(定时全备份+binlo...

3318
来自专栏乐沙弥的世界

Oracle 10.2.0.1 升级到 10.2.0.4

数据库升级并不难,只要遵循其步骤,一般问题不大。但是升级失败的情况也是屡见不鲜,尤其是生产数据库的升级,搞不定的时候甚至要创建SR。

1173
来自专栏Python

MySQL常见的库操作,表操作,数据操作集锦及一些注意事项

一 库操作(文件夹) 1 数据库命名规则 可以由字母、数字、下划线、@、#、$ 区分大小写 唯一性 不能使用关键字如 create select 不能单独使用数...

2259
来自专栏源哥的专栏

如何对表操作进行监控

我们在跟踪定位一些系统问题的时候,经常需要知道哪些地方对一个表进行了操作,查询代码很麻烦,其实有一个简单的方法,就是跟踪表, 如下语句,就可以给表t_table...

1032

扫码关注云+社区