做为DBA可能经常需要变更表结构,比如为表字段增加默认值,增加not null约束,可是11g前,为表增加not null约束是一件风险比较大的事情,如果表比较大,那么增加not null约束的时间跟做一次全表扫描的时间差不多,而且更重要的是,在这个过程中,不但会锁表,而且会锁住这个表的library cache 对象。会导致对这个表的查询都会挂起,继而出现library cache lock/pin等待。除了加not null约束外,增加主键约束也会因此同样的问题。此类问题解决的方案是,增加约束的时候,可以暂时指定约束的类型为enable noinvalidate的,这样就能使操作的时间足够短。
SESSION 1:执行DDL操作,表稍微大点,可以让这个操作的时间长点:
alter table wxh_tbd modify object_name not null;
SESSION 2:执行查询操作
select count(*) from wxh_tbd where rownum=1;
会发现连查询都会被hang住,后台在等待library cache lock.
根据v$session_wait的p1raw可以知道等待是发生在wxh_tbd表的handler上的
SQL> col KGLNAOBJ for a20
SQL> select KGLNAOBJ from x$kglob where kglhdadr = '2A7938F4';
KGLNAOBJ
--------------------
WXH_TBD
把此时的共享池dump出来
alter session set events 'immediate trace name library_cache level 10';
BUCKET 103643:
LIBRARY OBJECT HANDLE: handle=2a7938f4 mutex=2A7939A8(0)
name=SCOTT.WXH_TBD---------------------------------------LCO的名字,可以看到为wxh_tbd这个表对象
hash=cfe366d2fa989e1901d16ba139c394db timestamp=07-05-2011 11:12:47
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0701-0701 lock=X pin=X latch#=1 hpc=0002 hlc=0002
lwt=2A793950[307D2360,307D2360] ltm=2A793958[2A793958,2A793958]
pwt=2A793934[2A793934,2A793934] ptm=2A79393C[2A79393C,2A79393C]
ref=2A793970[2A793970,2A793970] lnd=2A79397C[2A76CC74,2A759F44]
DEPENDENCY REFERENCES:
reference latch flags
--------- ----- -------------------
2f1ccdd4 2 DEP[01]
2f1453f8 2 DEP[01]
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
307dad4c 32f3cc94 32f3cc94 1 X [00]------SESSION 1以x模式拥有了library cache lock
LOCK WAITERS:
lock user session count mode
-------- -------- -------- ----- ----
307d2344 32f2a014 32f2a014 0 S------------SESSION 2想以s模式拥有library cache lock但是不能获得,产生等待
PIN OWNERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
30785520 32f3cc94 32f3cc94 0 1 X 0701---------SESSION 1 以x模式拥有了library cache pin
共享池对象的HANDLER上有四个非常重要的队列,LOCK OWNERS,LOCK WAITERS,PIN OWNERS,PIN WAITERS。这几个队列是实现LIBRARY CACHE LOCK/PIN排队机制的重要结构。
SESSION 1在表上执行了DDL操作,因此在表的共享池对象上了加了X模式的library cache lock和X模式的library cache pin。
SESSION 2需要查询这个表的数据,需要获得表的共享池对象上的S模式的library cache lock,这个锁请求与SESSOIN 1的X模式不兼容,因此发生争用,产生了library cache lock等待。
问题到这里,似乎还是比较简单的,我们继续
SESSION 3:执行跟SESSION 2一样的查询
select count(*) from wxh_tbd where rownum=1;
这个时候查看后台等待,又多了一个library cache pin等待,根据p1raw参数,可以知道等待是发生在查询sql上的
SQL> col KGLNAOBJ for a80
SQL> select KGLNAOBJ from x$kglob where kglhdadr = '2A5E541C';
KGLNAOBJ
--------------------------------------------------------------------------------
select count(*) from wxh_tbd where rownum=1
alter session set events 'immediate trace name library_cache level 10';
BUCKET 51618:
LIBRARY OBJECT HANDLE: handle=2a7520c4 mutex=2A752178(1)
name=select count(*) from wxh_tbd where rownum=1--------------------LCO的名字
hash=a359d0118530b1534deb83cbbad4c9a2 timestamp=07-05-2011 11:10:39
namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/SML/KST/DBN/MTX/[120100d4]
kkkk-dddd-llll=0001-0001-0001 lock=N pin=0 latch#=3 hpc=0006 hlc=0006
lwt=2A752120[2A752120,2A752120] ltm=2A752128[2A752128,2A752128]
pwt=2A752104[2A752104,2A752104] ptm=2A75210C[2A75210C,2A75210C]
ref=2A752140[2A752140,2A752140] lnd=2A75214C[328DB424,2A75DCA8]
DEPENDENCY REFERENCES:
reference latch flags
--------- ----- -------------------
2f145048 0 [60]
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
30798c70 32f2a014 32f2a014 1 N [00]----------------SESSION 2以NULL模式获得了cursor上的library cache lock
30798b90 32f2b2dc 32f2b2dc 1 N [00]----------------SESSION 3以NULL模式获得了cursor上的library cache lock
LIBRARY OBJECT: bject=2f214e44
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
0 2f214dd0 2f214a84 2a751f80----------------子lco的handler,根据它从dump文件里找到游标的子lco
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 2a752054 2f214edc I/P/A/-/- 0 NONE 00
LIBRARY OBJECT HANDLE: handle=2a751f80 mutex=2A752034(0)--------------游标的子lco
namespace=CRSR flags=RON/KGHP/PN0/[10010000]
kkkk-dddd-llll=0000-0001-0000 lock=N pin=X latch#=3 hpc=0006 hlc=0006
lwt=2A751FDC[2A751FDC,2A751FDC] ltm=2A751FE4[2A751FE4,2A751FE4]
pwt=2A751FC0[307642C4,307642C4] ptm=2A751FC8[2A751FC8,2A751FC8]
ref=2A751FFC[2F214A84,2F214A84] lnd=2A752008[2A752008,2A752008]
CHILD REFERENCES:
reference latch flags
--------- ----- -------------------
2f214a84 0 CHL[02]
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
30798b20 32f2a014 32f2a014 1 N [00]---------------SESSION 2以NULL模式获得了cursor上的library cache lock
30793810 32f2b2dc 32f2b2dc 1 N [00]---------------SESSION 3以NULL模式获得了cursor上的library cache lock
PIN OWNERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
30764040 32f2a014 32f2a014 0 0 X 0041--------------SESSION 2以X模式获得了子lco上的library cache pin
PIN WAITERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
307642a8 32f2b2dc 32f2b2dc 0 0 S 0000--------------SESSION 3想以S模式获得了子LCO上的library cache pin产生等待
查看这个情况下,wxh_tbd下的lock有什么变化
BUCKET 103643:
LIBRARY OBJECT HANDLE: handle=2a7938f4 mutex=2A7939A8(0)
name=SCOTT.WXH_TBD
hash=cfe366d2fa989e1901d16ba139c394db timestamp=07-05-2011 11:12:47
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0701-0701 lock=X pin=X latch#=1 hpc=0002 hlc=0002
lwt=2A793950[307D2360,307D2360] ltm=2A793958[2A793958,2A793958]
pwt=2A793934[2A793934,2A793934] ptm=2A79393C[2A79393C,2A79393C]
ref=2A793970[2A793970,2A793970] lnd=2A79397C[2A76CC74,2A759F44]
DEPENDENCY REFERENCES:
reference latch flags
--------- ----- -------------------
2f1ccdd4 2 DEP[01]
2f1453f8 2 DEP[01]
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
307dad4c 32f3cc94 32f3cc94 1 X [00]
LOCK WAITERS:
lock user session count mode
-------- -------- -------- ----- ----
307d2344 32f2a014 32f2a014 0 S
PIN OWNERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
30785520 32f3cc94 32f3cc94 0 1 X 0701
发现SESSION 3根本就没有出现在WXH_TBD这个共享池对象的任何列表里,没有对wxh_tbd这个共享池对象产生任何影响
实验到这里我们基本可以总结出如下的顺序:
1)SESSION 1,获得了共享池对象wxh_tbd上的x模式的library cache lock和x模式的library cache pin
2)SESSION 2,首先获得cursor上的null模式的library cache lock和子lco上null模式的library cache lock和x模式的library cache pin,然后再
获得cursor的参照对象wxh_tbd上,s模式的library cache lock。由于session 1已经以x模式持有了library cache lock,因此发生等待.
3)session 3跟session 2的过程差不多。首先获得cursor上的null模式的library cache lock和子lco上null模式的library cache lock和x模式的library cache pin
但是由于session 2已经获得了子LCO上x模式的library cache pin,因此发生等待。但是这个过程似乎与dump出来的不符,因为dump出来的内容是,SESSION 3
等待的是s模式的library cache pin非X模式。ORACLE比较聪明啦,发现已经有会话在硬解析了,他自己就以S模式等着了,就像READ BY OTHER SESSION似的,发现
已经有会话在读取数据块了,自己就等着了,不去物理读了。SESSION 3之所以没产生wxh_tbd上的library cache lock等待,是因为还没到那一步,直接在cursor 子lco
的library cache pin上就堵住了
如果这个时候,存在大量并发的这个查询(SQL文本一样),后台就会产生大量的library cache pin.
如果发出的语句都不一样,那就是大量的library cache lock了,这个lock就是wxh_tbd上的。
会话 | CURSOR | WXH_TBD | ||
LIBRARY CACHE LOCK | LIBRARY CACHE PIN | LIBRARY CACHE LOCK | LIBRARY CACHE PIN | |
SESSION 1 | 不需要持有 | 不需要持有 | X | X |
SESSION 2 | NULL | X | S(等待) |
|
SESSION 3 | NULL | S(等待) |
|
|
因此我们为表字段增加not null约束的时候,风险还是比较大的,解决的办法就是指定约束的类型为enable novalidate,这样这个操作就会很快完成。风险就会比较小了。