library cache pin等待分析

最新推荐文章于 2024-02-22 14:32:34 发布

转载最新推荐文章于 2024-02-22 14:32:34 发布 · 67 阅读

本文深入探讨了Oracle数据库中并发查询时遇到的library cache lock和pin等待问题，详细分析了不同会话之间的交互及锁等待的顺序，揭示了Oracle如何智能地处理这些情况，以避免阻塞并发操作。

上次跟大家分享的时候，我说如果有DDL没执行完，会导致所有对它的操作被hang在library cache lock上。
可是前几天CRM库上在执行DDL期间，后台绝大多数的等待是library cache pin。把这个错误纠正一下。

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 a20
SQL> select KGLNAOBJ from x$kglob where kglhdadr = '2A7938F4';

KGLNAOBJ
--------------------
WXH_TBD

alter session set events 'immediate trace name library_cache level 10';----------dump出来看看
BUCKET 103643:
  LIBRARY OBJECT HANDLE: handle=2a7938f4 mutex=2A7939A8(0)
  name=SCOTT.WXH_TBD ----------------------------------------------------LCO的名字
  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

问题到这里，似乎还是比较简单的，我们继续

SESSION 3:执行跟SESSION 2一样的查询
select count(*) from wxh_tbd where rownum=1;
这个时候查看后台等待，又多了一个library cache pin等待，根据p1raw参数，可以知道等待是发生在查询sql上的

SQL> col KGLNAOBJ for a80
SQL> 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';----------dump出来看看
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

-----------------游标的子lco
  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产生任何影响

我们基本可以总结出如下的顺序：
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等待，是因为还没到那一步，直接在子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			X	X
SESSION 2	NULL	X	S
SESSION 3	NULL	S