说起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;这种语句虽然非常便捷，但是不一定适合在大并发的时候去执行。我们应该把这个操作分解成几步来做，

dbms_stats是oracle用来代替原有的analyze功能的一个包，与analyze相比dbms_stats具有很多优势，比如并行，比如分区信息统计等，但是dbms_stats再分析index的时候处理方式并不是太理想，dbms_stats分析index时将会只统计leaf_blocks为当前有数据的leaf block，而analyze则会统计为所有曾经被使用过的leaf block number，很显然dbms_stats的统计结果会使index fast full scan的成本被严重低估，在某些情况下会错误得选择index fast full scan做为执行路径。下面来看一个例子：

 

先清空原来的表

SQL 10G>truncate table t1;

Table truncated.

 

插入数据

SQL 10G>insert into t1 select
  2  rownum id,
  3  trunc(100 * dbms_random.normal) val,
  4  rpad('x',100) padding
  5  from
  6  all_objects
  7  where
  8  rownum <= 10000
  9  ;

10000 rows created.

 

SQL 10G>commit;

Commit complete.

 

创建索引，并限制pctfree为99，模拟大索引的产生

SQL 10G>create index ind_t1 on t1(id) pctfree 99;

Index created.

 

使用analyze分析索引

SQL 10G>ANALYZE INDEX IND_T1 COMPUTE STATISTICS;

Index analyzed.

 

SQL 10G>SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS FROM  user_indexes where table_name='T1';

INDEX_NAME                         BLEVEL LEAF_BLOCKS
------------------------------ ---------- -----------
IND_T1                                  2       10000

 

再使用dbms_stats分析，可以看到在这个时候基本统计数据是相同的

SQL 10G>EXEC DBMS_STATS.GATHER_INDEX_STATS('TEST','IND_T1');

PL/SQL procedure successfully completed.

 

SQL 10G>SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS FROM  user_indexes where table_name='T1';

INDEX_NAME                         BLEVEL LEAF_BLOCKS
------------------------------ ---------- -----------
IND_T1                                  2       10010

 

删除数据使表中只保留一条记录

SQL 10G>delete from t1 where rownum<10000;

9999 rows deleted.

 

SQL 10G>commit;

Commit complete.

 

再用analyze分析索引，可以发现leaf_blocks依然是10000

SQL 10G>ANALYZE INDEX IND_T1 COMPUTE STATISTICS;

Index analyzed.

 

SQL 10G>SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS FROM  user_indexes where table_name='T1';

INDEX_NAME                         BLEVEL LEAF_BLOCKS
------------------------------ ---------- -----------
IND_T1                                  2       10000

 

看cost这一项显示index fast full scan的成本为2679，这是正确的

SQL 10G>set autotrace trace exp;

SQL 10G>select/*+ index_ffs(t1,ind_t1)*/ count(*) from t1;

Execution Plan

-------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Cost (%CPU)|
-------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |  2679  (19)|
|   1 |  SORT AGGREGATE       |        |     1 |            |
|   2 |   INDEX FAST FULL SCAN| IND_T1 |     1 |  2679  (19)|
-------------------------------------------------------------

 

使用dbms_stats分析索引，leaf_blocks被统计为1，只统计了当前在用的leaf block

SQL 10G>EXEC DBMS_STATS.GATHER_INDEX_STATS('TEST','IND_T1');

PL/SQL procedure successfully completed.

 

SQL 10G>set autotrace off;

SQL 10G>SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS FROM  user_indexes where table_name='T1';

INDEX_NAME                         BLEVEL LEAF_BLOCKS
------------------------------ ---------- -----------
IND_T1                                  2           1

 

看cost这一项显示index fast full scan的成本为1，这显然是出现了错误

SQL 10G>set autotrace trace;
SQL 10G>select/*+ index_ffs(t1,ind_t1)*/ count(*) from t1;

Execution Plan

-------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Cost (%CPU)|
-------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |     1   (0)|
|   1 |  SORT AGGREGATE       |        |     1 |            |
|   2 |   INDEX FAST FULL SCAN| IND_T1 |     1 |     1   (0)|
-------------------------------------------------------------

 

再来看看它究竟需要读取多少个块，是不是cost＝1就够了

 

SQL 10G>   ALTER SESSION SET EVENTS 'immediate trace name flush_cache';

Session altered.

SQL 10G>select/*+ index_ffs(t1,ind_t1)*/ count(*) from t1;

Execution Plan
----------------------------------------------------------

-------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Cost (%CPU)|
-------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |     1   (0)|
|   1 |  SORT AGGREGATE       |        |     1 |            |
|   2 |   INDEX FAST FULL SCAN| IND_T1 |     1 |     1   (0)|
-------------------------------------------------------------

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10035  consistent gets
      10016  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

显然，这里发生了10016个physical reads，cost＝1是远远不够的。不知道oracle会不会就这个问题有改进方案，大家拭目以待。