为表字段增加 not null约束的风险

来源：互联网发布：极光推送golang 编辑：程序博客网时间：2024/05/22 13:22

做为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 a20SQL> 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 a80SQL> 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

不需要持有

SESSION 2

NULL