library cache lock wait event

说起library cache lock wait event就必须先说一下library cache lock是什么东西，library lock是oracle用来对library cache object进行并发控制的两种数据结构之一，另外一种数据结构是pin，lock将会在pin之前获得，并被加载到library cache handle上。library cache lock有3种形式，share,null,exclusive，具体的library cache lock的分析请看我的另一篇帖子。

通常我们举的关于library cache lock wait event的例子都是用procedure来模拟的，eygle发表过这方面的文章，今天我举的是另一个例子，也是一次真实的事故，事故发生的原因就是因为ddl导致cursor invalidation,但是ddl持续运行了很长时间，导致后来的session需要parse sql的时候不能获得library cache lock，进一步导致app服务器的connection pool满，导致应用crash。下面我们用一个简单的例子模拟一下这个场景。

session 1:

SQL 10G>alter table test add constraint uk_test unique(a,b,c,d,e) using index online compute statistics;

Table altered.

session 2:

SQL 10G>select sid from v$mystat where rownum<2;

SID
----------
142

SQL 10G>select count(*) from test;

session 3:
SQL 10G> select event from v$session_wait where sid=142;
EVENT
--------------------------------------------------------------------------------
library cache lock

当test表过大，创建uk constraint时间过长时会有越来越多的session等待library cache lock从而进一步导致应用crash，这样提醒我们当对大表进行ddl时一定要分解ddl操作，不要一句语句搞定所有操作，象alter table test add constraint uk_test unique(a,b,c,d,e) using index online compute statistics;这种语句虽然非常便捷，但是不一定适合在大并发的时候去执行。我们应该把这个操作分解成几步来做，

1. create index ind_test on test(a,b,c,d,e) online compute statistics;

2. alter table test add constraint uk_test unique(a,b,c,d,e) using index ind_test novalidate;

3. 校验表里面的数据是否符合唯一约束

4. alter table test modify constraint uk_test validate;

这里的要点就是要把每一个ddl分解成小的操作来执行。值得指出的一点是alter table test modify constraint uk_test validate;这个步骤虽然会可能会耗时非常久，但是这个步骤是不会加载library cache exclusive lock的，所以它不会导致其他session等待library cache lock。但是constraint的状态从disable到enable的转换则还是会加载library cache exclusive lock，导致其他进程等待library cache lock。oracle可能在这个地方做了不同的处理，针对disable->enable和novalidate->validate采取了不同的lock类型，显然我们也可以知道disable->enable和novalidate->validate对library cache object的影响是不一样的，一个是可以说是从无到有，一个是只是改变了一下状态，我的猜测是disable->enable的话oracle依然加载了exclusive lock，而novalidate->validate的话oracle加载了null or share lock,有心的人可以dump一下sga来测试一下。