Oracle数据库里什么情况下select操作会hang住
Posted: June 28, 2012 | Author: Cui Hua | 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住。
是的,在生产库的大表上加唯一索引操作那是找死。