外键约束列并没有导致大量建筑指数library cache pin/library cache lock

清除一个100大数据表超过一百万线，发现已经运行了几个小时：

delete B001.T_B11;

由下面的SQL跟踪，发现经常发生library cache pin和library cache lock的等待，怀疑有大量的recursive sql在运行。于是对这个session做了10046：

发现有大量的例如以下SQL运行，每删除1行T_B11，都会运行以下2条SQL一次，

PARSING IN CURSOR #3 len=93 dep=2 uid=0 oct=3 lid=0 tim=1435131097407618 hv=2174374139 ad='b4b86f9e0' sqlid='a16ztda0tnn7v'

 select /*+ all_rows */ count(1) from "B001"."T_BA19" where "BID" = :1

END OF STMT

BINDS #3:

 Bind#0

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=12 fl2=0001 frm=00 csi=00 siz=24 off=0

  kxsbbbfp=2b20a2af4d90  bln=22  avl=04  flg=05

  value=232156

EXEC #3:c=0,e=198,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=4133059621,tim=1435131097407770

FETCH #3:c=0,e=39,p=0,cr=1,cu=0,mis=0,r=1,dep=2,og=1,plh=4133059621,tim=1435131097407841

CLOSE #3:c=0,e=3,dep=2,type=3,tim=1435131097407880

=====================

PARSING IN CURSOR #3 len=87 dep=2 uid=0 oct=3 lid=0 tim=1435131097410498 hv=2660531033 ad='b49211628' sqlid='1mhux5ug98yut'

 select /*+ all_rows */ count(1) from "B001"."T_BA18" where "BID" = :1

END OF STMT

BINDS #3:

 Bind#0

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=12 fl2=0001 frm=00 csi=00 siz=24 off=0

  kxsbbbfp=2b20a2af4d90  bln=22  avl=04  flg=05

  value=232156

EXEC #3:c=0,e=193,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=3617316486,tim=1435131097410645

FETCH #3:c=6000,e=5402,p=0,cr=1342,cu=0,mis=0,r=1,dep=2,og=1,plh=3617316486,tim=1435131097416075

CLOSE #3:c=0,e=2,dep=2,type=3,tim=1435131097416182

T_BA18与T_BA19都是有外键与T_B11相关联的。看到这里。基本上确定T_BA18、T_BA19相应的外键列上没有创建索引导致delete主表缓慢。

由于每删除主表的一行，都会去子表进行验证查询，而子表列上没有创建索引的话，会导致查询缓慢。

而且在此过程会对子表施加S锁，这就是library cache pin的由来。

（oracle对library cache pin的解释中有一句很经典的话：

An X request (3) will be blocked by anypins held S mode (2) on the object.

An S request (2) will be blocked by any X mode (3) pin held, or may queue behind some other X request.）

两个子表的BID后列添加到索引。问题解决了。

版权声明：本文博主原创文章，博客，未经同意不得转载。