←
→

Oracle数据库里什么情况下select操作会hang住

Posted: June 28, 2012 | Author: | Filed under: Oracle | 1 Comment »

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

 

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

 

我们来举一个反例。

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

 

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

#define KGLTRCLCK  0×0010                       /* trace lock operations */

#define KGLTRCPIN  0×0020                       /* trace pin operations  */

#define KGLTRCOBF  0×0040                       /* trace object freeing  */

#define KGLTRCINV  0×0080                       /* trace invalidations   */

#define KGLDMPSTK  0×0100                  /* DUMP CALL STACK WITH TRACE */

#define KGLDMPOBJ  0×0200                  /* DUMP KGL OBJECT WITH TRACE */

#define KGLDMPENQ  0×0400                 /* DUMP KGL ENQUEUE WITH TRACE */

#define KGLTRCHSH  0×2000                          /* DUMP BY HASH VALUE */

 

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

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

 

TO_NUMBER(‘210′,’XXXX’)

———————–

                    528

 

先在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

 

c:\app\cuihua\diag\rdbms\cuihua112\cuihua112\trace\cuihua112_ora_2292.trc没有任何内容

看起来似乎是10049事件对11gR2无效或者Oracle改变了10049事件在11gR2中的level的定义（这个我不确定）。

 

我们换一个10gR2的版本：

SQL> select * from v$version;

 

BANNER

—————————————————————-

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod

PL/SQL Release 10.2.0.1.0 – Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version 10.2.0.1.0 – Production

NLSRTL Version 10.2.0.1.0 – Production

 

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）中从前到后可以看到如下内容：

KGLTRCLCK kglget     hd = 0x25788788  KGL Lock addr = 0x317954E8 mode = N

LIBRARY OBJECT HANDLE: handle=25788788 mutex=2578883C(0)

name=select count(*) from scott.emp

 

KGLTRCLCK kglget     hd = 0x2578848C  KGL Lock addr = 0x31797C08 mode = S

LIBRARY OBJECT HANDLE: handle=2578848c mutex=25788540(0)

name=SCOTT.EMP

 

KGLTRCLCK kglget     hd = 0x2578848C  KGL Lock addr = 0x31797C08 mode = S

LIBRARY OBJECT HANDLE: handle=2578848c mutex=25788540(0)

name=SCOTT.EMP

 

KGLTRCLCK kglget     hd = 0x2578848C  KGL Lock addr = 0x31797C08 mode = S

LIBRARY OBJECT HANDLE: handle=2578848c mutex=25788540(0)

name=SCOTT.EMP

 

KGLTRCLCK kglget     hd = 0x2578848C  KGL Lock addr = 0x31797C08 mode = S

LIBRARY OBJECT HANDLE: handle=2578848c mutex=25788540(0)

name=SCOTT.EMP

 

KGLTRCLCK kgllkdl    hd = 0x2578848C  KGL Lock addr = 0x31797C08 mode = S

LIBRARY OBJECT HANDLE: handle=2578848c mutex=25788540(0)

name=SCOTT.EMP

 

KGLTRCLCK kgllkdl    hd = 0x25788788  KGL Lock addr = 0x317954E8 mode = N

LIBRARY OBJECT HANDLE: handle=25788788 mutex=2578883C(1)

name=select count(*) from scott.emp

 

即针对上述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的先后持有模式为:

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x31B194AC mode = X

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x3173D1E4 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x31B194AC mode = X

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

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

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

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

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x31B194AC mode = X

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x31B194AC mode = X

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

这也就意味着在添加主键的整个过程中，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;

 

       SID STATISTIC#      VALUE

———- ———- ———-

       138          0          1

      

在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;

 

EVENT                          STATE               SECONDS_IN_WAIT

—————————— ——————- —————

library cache lock                     WAITING                      8

 

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

 

EVENT                          STATE               SECONDS_IN_WAIT

—————————— ——————- —————

library cache lock                     WAITING                      9

 

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

 

EVENT                          STATE               SECONDS_IN_WAIT

—————————— ——————- —————

library cache lock                     WAITING                      11

 

从中可以看到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;

Name           Type          Nullable Default Comments

————– ————- ——– ——- ——–

OWNER          VARCHAR2(30)  Y                        

OBJECT_NAME    VARCHAR2(128) Y                        

SUBOBJECT_NAME VARCHAR2(30)  Y                        

OBJECT_ID      NUMBER        Y                        

DATA_OBJECT_ID NUMBER        Y                        

OBJECT_TYPE    VARCHAR2(19)  Y                        

CREATED        DATE          Y                        

LAST_DDL_TIME  DATE          Y                        

TIMESTAMP      VARCHAR2(19)  Y                        

STATUS         VARCHAR2(7)   Y                        

TEMPORARY      VARCHAR2(1)   Y                        

GENERATED      VARCHAR2(1)   Y                        

SECONDARY      VARCHAR2(1)   Y                        

 

SQL> select count(*) from t1;

 

  COUNT(*)

———-

    592951

 

同时开两个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;

 

OWNER                          OBJECT_NAME                    OBJECT_TYPE

—————————— —————————— ——————-

SYS                            CON$                           TABLE

SYS                            I_COL2                         INDEX

SYS                            I_USER#                        INDEX

SYS                            C_TS#                          CLUSTER

SYS                            I_OBJ#                         INDEX

SYS                            I_CON2                         INDEX

SYS                            IND$                           TABLE

SYS                            BOOTSTRAP$                     TABLE

SYS                            UET$                           TABLE

 

9 rows selected

 

从session 1所产生的trace文件（d:\oracle\admin\cuihua\udump\cuihua_ora_5020.trc）中我们可以看出对表t1的library cache lock的先后持有模式为：

KGLTRCLCK kglget     hd = 0x2557AC18  KGL Lock addr = 0x31B1A00C mode = S

LIBRARY OBJECT HANDLE: handle=2557ac18 mutex=2557ACCC(0)

name=SCOTT.T1

 

KGLTRCLCK kglget     hd = 0x2557AC18  KGL Lock addr = 0x31B196DC mode = S

LIBRARY OBJECT HANDLE: handle=2557ac18 mutex=2557ACCC(0)

name=SCOTT.T1

 

KGLTRCLCK kgllkdl    hd = 0x2557AC18  KGL Lock addr = 0x31B196DC mode = S

LIBRARY OBJECT HANDLE: handle=2557ac18 mutex=2557ACCC(0)

name=SCOTT.T1

 

KGLTRCLCK kgllkdl    hd = 0x2557AC18  KGL Lock addr = 0x31B1A00C mode = S

LIBRARY OBJECT HANDLE: handle=2557ac18 mutex=2557ACCC(0)

name=SCOTT.T1

 

KGLTRCLCK kgllkdl    hd = 0x2557AC18  KGL Lock addr = 0x31B196DC mode = N

LIBRARY OBJECT HANDLE: handle=2557ac18 mutex=2557ACCC(0)

name=SCOTT.T1

 

KGLTRCLCK kglget     hd = 0x2557AC18  KGL Lock addr = 0x31B1A00C mode = X

LIBRARY OBJECT HANDLE: handle=2557ac18 mutex=2557ACCC(0)

name=SCOTT.T1

 

KGLTRCLCK kgllkdl    hd = 0x2557AC18  KGL Lock addr = 0x31B1A00C mode = X

LIBRARY OBJECT HANDLE: handle=2557ac18 mutex=2557ACCC(0)

name=SCOTT.T1

 

即大部分时间对表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的先后持有模式为:

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x31B19C8C mode = X

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kglget     hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x31B64670 mode = N

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

 

KGLTRCLCK kgllkdl    hd = 0x2597D07C  KGL Lock addr = 0x31B19C8C mode = X

LIBRARY OBJECT HANDLE: handle=2597d07c mutex=2597D130(0)

name=SCOTT.T2

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

 

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

 

One Comment on “Oracle数据库里什么情况下select操作会hang住”

  1. miki西游 says:
    June 28, 2012 at 5:41 pm

    是的，在生产库的大表上加唯一索引操作那是找死。

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

←
→