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

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

 

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

 

我们来举一个反例。

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

 

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

#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中我们可以看出对表t2library 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住的。

因为一开头的Xkglget,结尾才kgllkdlkgllkdl大致是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)操作。

 

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

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,同时它的STATEwaitingSECONDS_IN_WAIT的值在递增。

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

 

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

 

答案是:不是这样的。

 

我们来举一个反例。

现在我们来测一下对表drop一个columnlibrary 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事件后dropt1的列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 2session 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中我们可以看出对表t1library 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.t1library cache lock的持有模式都是S,最后才是X,所以这就可以解释为什么在对表scott.t1执行drop column操作的时候对它的select语句能够同时执行。

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

 

最后我们来测一下对一个表增加一个unique constraintlibrary 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.t2library 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:

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


Leave a Reply

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