library cache lock 问题

baolei

ML 你好：

   遇到大量ibrary cache lock等待问题：

COUNT(*) EVENT
---------- ----------------------------------------------------------------
         1 GCR sleep
         1 PING
         1 SQL*Net message to client
         2 gcs remote message
         2 Space Manager: slave idle wait
        29 SQL*Net message from client
         1 pmon timer
         2 DIAG idle wait
         1 enq: FB - contention
        22 rdbms ipc message
         1 VKTM Logical Idle Wait
         1 ges remote message
         1 wait for unread message on broadcast channel
         1 enq: CO - master slave det
         1 Streams AQ: qmn slave idle wait
      2359 library cache lock
         2 DFS lock handle
         1 Streams AQ: qmn coordinator idle wait
         1 class slave wait
         1 Streams AQ: waiting for time management or cleanup tasks

20 rows selected.

上传了library cache lock session 和 x$KGLLK 信息。 其中sql_id 已经从awr 和v$sql 中找不到了。不知道程序在执行什么语句如何定位源头，从KGLLKMOD 》0 列中发现sid都在不断变，应用说已经停止进行了..

Maclean Liu(刘相兵

1、你excel里方的是 v$session的信息吧  需要的是ASH dba_hist_active_sess_history视图里相关的信息，保存为excel

2、做一个library_cache

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump library_cache 10;

并压缩上传trace文件

baolei

做到systemdump：

SO: 0x7000005c162dc28, type: 78, owner: 0x700000647ef22b0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
         proc=0x7000006551fe8f8, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8548 ID:, pg=0

        LibraryObjectLock:  Address=7000005c162dc28 Handle=7000005d15c8f80 RequestMode=X CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0           
          ClusterLock=70000064522c400 User=700000651ca6950 Session=700000651ca6950 ReferenceCount=0           
          Flags=[0000] SavepointNum=4
        LibraryHandle:  Address=7000005d15c8f80 Hash=cd6169b0 LockMode=X PinMode=0 LoadLockMode=0 Status=VALD
          ObjectName:  Name=44   
            FullHashValue=cf55ef756335dc3c88caa092cd6169b0 Namespace=ACCOUNT_STATUS(79) Type=NONE(255) Identifier=0 OwnerIdn=2147483644
          Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=0 ActiveLocks=1 TotalLockCount=14429 TotalPinCount=0
          Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 BucketInUse=31538 HandleInUse=31538 HandleReferenceCount=0
          Concurrency:  DependencyMutex=7000005d15c9030(0, 1, 0, 0) Mutex=7000005d15c90b0(0, 109677353, 14501, 0)
          Flags=PIN/TIM/[00002801]
          WaitersLists:  
            Lock=7000005d15c9010[7000005b8bbdca8,7000005b90a64d8]
            Pin=7000005d15c8ff0[7000005d15c8ff0,7000005d15c8ff0]
            LoadLock=7000005d15c9068[7000005d15c9068,7000005d15c9068]
          Timestamp:  Current=NULL
          LockInstance:  id='LBcf55ef756335dc3c' GlobalEnqueue=0 ReleaseCount=0
          PinInstance:  id='NBcf55ef756335dc3c' GlobalEnqueue=0       ----------------------------------------
      SO: 0x7000005c162db28, type: 78, owner: 0x7000005bb1b6268, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
       proc=0x7000006551fe8f8, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8548 ID:, pg=0

      LibraryObjectLock:  Address=7000005c162db28 Handle=7000005d15c8f80 RequestMode=S CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0         
        
        User=700000651ca6950 Session=700000651ca6950 ReferenceCount=0 Flags=[0000] SavepointNum=3
      LibraryHandle:  Address=7000005d15c8f80 Hash=cd6169b0 LockMode=X PinMode=0 LoadLockMode=0 Status=VALD
        ObjectName:  Name=44   
          FullHashValue=cf55ef756335dc3c88caa092cd6169b0 Namespace=ACCOUNT_STATUS(79) Type=NONE(255) Identifier=0 OwnerIdn=2147483644
        Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=0 ActiveLocks=1 TotalLockCount=14429 TotalPinCount=0
        Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 BucketInUse=31538 HandleInUse=31538 HandleReferenceCount=0
        Concurrency:  DependencyMutex=7000005d15c9030(0, 1, 0, 0) Mutex=7000005d15c90b0(0, 109677353, 14501, 0)
        Flags=PIN/TIM/[00002801]
        WaitersLists:  
          Lock=7000005d15c9010[7000005b8bbdca8,7000005b90a64d8]
          Pin=7000005d15c8ff0[7000005d15c8ff0,7000005d15c8ff0]
          LoadLock=7000005d15c9068[7000005d15c9068,7000005d15c9068]
        Timestamp:  Current=NULL
        LockInstance:  id='LBcf55ef756335dc3c' GlobalEnqueue=0 ReleaseCount=0
        PinInstance:  id='NBcf55ef756335dc3c' GlobalEnqueue=0   

baolei

其中发现：

LibraryHandle:  Address=7000005d15c8f80 Hash=cd6169b0 LockMode=X PinMode=0 LoadLockMode=0 Status=VALD
          ObjectName:  Name=44   
            FullHashValue=cf55ef756335dc3c88caa092cd6169b0 Namespace=ACCOUNT_STATUS(79) Type=NONE(255) Identifier=0 OwnerIdn=2147483644
          Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=0 ActiveLocks=1 TotalLockCount=14429 TotalPinCount=0
          Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 BucketInUse=31538 HandleInUse=31538 HandleReferenceCount=0
          Concurrency:  DependencyMutex=7000005d15c9030(0, 1, 0, 0) Mutex=7000005d15c90b0(0, 109677353, 14501, 0)
          Flags=PIN/TIM/[00002801]
          WaitersLists:  

oracle方回复说是用户密码错误导致。。。

harryzhang

11.2的库? 有无时间段的AWR

之前遇到过的一次记录。

1* select sid,event,sql_id,username from v$session where event='library cache lock'

       SID EVENT                          SQL_ID        USERNAME
---------- ------------------------------ ------------- ------------------------------
       382 library cache lock
       612 library cache lock
      2661 library cache lock

Maclean Liu(刘相兵

ACCOUNT_STATUS 确实和密码不正确有关