深入内核：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 住。