外鍵約束列沒建索引導致大量library cache pin/library cache lock，librarypin

外鍵約束列沒建索引導致大量library cache pin/library cache lock，librarypin
外鍵約束列沒建索引導致大量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列添加索引之後，問題解決。