ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 1250|回复: 1

[原创] 12C library cache lock & library cache pin 模拟测试

[复制链接]
论坛徽章:
5
娜美
日期:2017-06-26 15:18:15目光如炬
日期:2017-08-20 22:00:00目光如炬
日期:2017-08-27 22:00:01火眼金睛
日期:2017-09-01 17:00:07目光如炬
日期:2017-09-03 22:00:01
发表于 2017-6-8 16:13 | 显示全部楼层 |阅读模式
本帖最后由 sunyunyi 于 2017-8-18 13:53 编辑

12C library cache lock & library cache pin 模拟测试

library cache lock
This event controls the concurrency between clients of the library cache. It acquires a
lock on the object handle so that either:
• One client can prevent other clients from accessing the same object
• The client can maintain a dependency for a long time (for example, no other client
can change the object)
This lock is also obtained to locate an object in the library cache.
Wait Time: 3 seconds (1 second for PMON)
Parameter Description
handle address Address of the object being loaded
lock address Address of the load lock being used. This is not the same thing as a
latch or an enqueue, it is a State Object.
mode Indicates the data pieces of the object which must be loaded


library cache pin
This event manages library cache concurrency. Pinning an object causes the heaps to
be loaded into memory. If a client wants to modify or examine the object, the client
must acquire a pin after the lock.
Wait Time: 3 seconds (1 second for PMON)
Parameter Description
handle address Address of the object being loaded
pin address Address of the load lock being used. This is not the same thing as a
latch or an enqueue, it is basically a State Object.
mode Indicates which data pieces of the object that must be loaded


DDL操作都需要library cache lock X 和 library cache pin X
执行存储过程library cache lock N  library cache pin S
需要注意mutex只是替代了library cache lock latch 和 library cache pin latch ,library cache lock 和library cache pin 并没有改变这一点需要明确!!


测试环境:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

session A: 64

create or replace procedure lock_test
is
begin
sys.dbms_lock.sleep(5000);
end;
/
SQL> exec lock_test;
SQL> ALTER SESSION SET EVENTS 'immediate trace name library_cache level 16';

Bucket: #=116413 Mutex=0x825d4e40(111669149696, 7100, 0, 6)
  LibraryHandle:  Address=0x6a6e6500 Hash=e487c6bd LockMode=N PinMode=S LoadLockMode=0 Status=VALD
   ObjectName:  Name=CDB$ROOT.SYS.LOCK_TEST   
      FullHashValue=93d3eb836a7cc834b1ad6ebee487c6bd Namespace=TABLE/PROCEDURE(01) Type=PROCEDURE(07) ContainerId=1 ContainerUid=1 Identifier=88559 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=1 TotalLockCount=7 TotalPinCount=4
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=73 HandleInUse=73 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x6a6e65b0(0, 7, 0, 0) Mutex=0x6a6e6650(26, 7649, 0, 6)
    Flags=PIN/TIM/[00002801] Flags2=[0000]
    WaitersLists:  
      Lock=0x6a6e6590[0x6a6e6590,0x6a6e6590]
      Pin=0x6a6e6570[0x6a6e6570,0x6a6e6570]
      LoadLock=0x6a6e65e8[0x6a6e65e8,0x6a6e65e8]
    Timestamp:  Current=06-08-2017 11:45:39
    HandleReference:  Address=0x6a6e66d8 Handle=0x808a3310 Flags=OWN[200]
    ReferenceList:  
      Reference:  Address=0x80272eb0 Handle=0x78cc8d40 Flags=DEP[01]
        Timestamp=06-08-2017 11:45:39 InvalidatedFrom=0
    LibraryObject:  Address=0x7df0e260 HeapMask=0000-0115-0115-0000 Flags=EXS/LOC[0004] Flags2=[8000000] PublicFlags=NST[0001]
      Dependencies:  count='3' size='16' table='0x7df0f108'
        Dependency:  num='0'
          Reference=0x7df0e4c8 Position=0 Flags=DEP[0001]
          Handle=0x7efe5508 Type=PACKAGE(09) Parent=CDB$ROOT.SYS.STANDARD   
        Dependency:  num='1'
          Reference=0x7df0e508 Position=0 Flags=DEP[0001]
          Handle=0x7def8f30 Type=PACKAGE(09) Parent=CDB$ROOT.SYS.DBMS_LOCK   
        Dependency:  num='2'
          Reference=0x7df0e570 Position=0 Flags=DEP[0001]
          Handle=0x6550d228 Type=PACKAGE(09) Parent=CDB$ROOT.SYS.SYS_STUB_FOR_PURITY_ANALYSIS   
      Accesses:  count='1' size='16'
        Dependency:  num='1' Type=000c
      DataBlocks:  
        Block:  #='0' name=KGLH0^e487c6bd pins=0 Change=NONE   
          Heap=0x7dcba6f8 Pointer=0x7df0e380 Extent=0x7df0e1b8 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=1.812500 Size=3.976562 LoadTime=611979361
        Block:  #='2' name=PLDIA^e487c6bd pins=0 Change=NONE   
          Heap=0x7df0e610 Pointer=0x7f28be78 Extent=0x7f28bdd0 Flags=I/-/-/A/-/-/-
          FreedLocation=0 Alloc=13.148438 Size=16.000000 LoadTime=611979361
        Block:  #='4' name=PLMCD^e487c6bd pins=1 Change=NONE   
          Heap=0x7df0e6e8 Pointer=0x7f285df8 Extent=0x7f285d50 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=1.078125 Size=4.000000 LoadTime=611979363
        Block:  #='8' name=KGLS^e487c6bd pins=1 Change=NONE   
          Heap=0x7df0e7c0 Pointer=0x7f285c98 Extent=0x7f284d50 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=0.156250 Size=4.000000 LoadTime=611979365

Bucket: #=54251 Mutex=0x82375d70(171798691840, 4213, 0, 6)
  LibraryHandle:  Address=0x7536b238 Hash=cb04d3eb LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=BEGIN lock_test; END;

      FullHashValue=ba8692bc7457ca4f22434318cb04d3eb Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1 ContainerUid=1 Identifier=3406091243 OwnerIdn=0
    Statistics:  InvalidationCount=1 ExecutionCount=1 LoadCount=2 ActiveLocks=1 TotalLockCount=2 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x7536b2e8(0, 2, 0, 0) Mutex=0x7536b388(40, 4063, 0, 6)
    Flags=RON/PIN/TIM/PN0/DBN/[10012841] Flags2=[0000]
    WaitersLists:  
      Lock=0x7536b2c8[0x7536b2c8,0x7536b2c8]
      Pin=0x7536b2a8[0x7536b2a8,0x7536b2a8]
      LoadLock=0x7536b320[0x7536b320,0x7536b320]
    Timestamp:  Current=06-08-2017 11:49:00
    HandleReference:  Address=0x7536b410 Handle=(nil) Flags=[00]
    ReferenceList:  
      Reference:  Address=0x7bde2708 Handle=0x784007f0 Flags=ROD[21]
    LibraryObject:  Address=0x7870b0f8 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
      DataBlocks:  
        Block:  #='0' name=KGLH0^cb04d3eb pins=0 Change=NONE   
          Heap=0x786fbd98 Pointer=0x7870b1c8 Extent=0x7870b050 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=2.539062 Size=3.976562 LoadTime=609587266
      ChildTable:  size='16'
        Child:  id='0' Table=0x7870bf78 Reference=0x7870ba40 Handle=0x6ac83c70
      Children:  
        Child:  childNum='0'
          LibraryHandle:  Address=0x6ac83c70 Hash=0 LockMode=N PinMode=S LoadLockMode=0 Status=INVL
            Name:  Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1
            Statistics:  InvalidationCount=1 ExecutionCount=1 LoadCount=1 ActiveLocks=1 TotalLockCount=1 TotalPinCount=2
            Counters:  BrokenCount=2 RevocablePointer=2 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
            Concurrency:  DependencyMutex=0x6ac83d20(0, 1, 0, 0) Mutex=0x7536b388(40, 4063, 0, 6)
            Flags=RON/PIN/FUL/PN0/EXP/CHD/[14012111] Flags2=[0000]
            WaitersLists:  
              Lock=0x6ac83d00[0x6ac83d00,0x6ac83d00]
              Pin=0x6ac83ce0[0x6ac83ce0,0x6ac83ce0]
              LoadLock=0x6ac83d58[0x6ac83d58,0x6ac83d58]
            ReferenceList:  
              Reference:  Address=0x7870ba40 Handle=0x7536b238 Flags=CHL[02]
            LibraryObject:  Address=0x6d449010 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[8080000] PublicFlags=NST[0001]
              Dependencies:  count='1' size='16' table='0x6d449e20'
                Dependency:  num='0'
                  Reference=0x6d4497f8 Position=0 Flags=DEP[0001]
                  Handle=0x6a6e6500(此处应用依赖对象) Type=PROCEDURE(07) Parent=CDB$ROOT.SYS.LOCK_TEST   
              ReadOnlyDependencies:  count='1' size='16'
                ReadDependency:  num='0' Table=0x6d449eb8 Reference=0x6d4496f0 Handle=0x784007f0 HandleFlag=0x12805 RefFlags=DEP/ROD/KPP[61]
              Accesses:  count='1' size='16'
                Dependency:  num='0' Type=000c
              Translations:  count='1' size='16'
                Translation:  num='0' Original=0x6a6e6500 Final=0x6a6e6500
              DataBlocks:  
                Block:  #='0' name=KGLH0^cb04d3eb pins=0 Change=NONE   
                  Heap=0x64fa29f8 Pointer=0x6d4490e0 Extent=0x6d448f68 Flags=I/-/P/A/-/-/-
                  FreedLocation=0 Alloc=2.773438 Size=3.937500 LoadTime=609587266
                Block:  #='4' name=PL/SQL MPCODE pins=0 Change=NONE   
                  Heap=0x7fe888c0 Pointer=0x7ce6aa18 Extent=(nil) Flags=I/-/-/A/-/-/E
                  FreedLocation=0 Alloc=0.000000 Size=0.000000 LoadTime=609587448
                Block:  #='5' name=PL/Scope Ident pins=0 Change=NONE   
                  Heap=0x7fe885a0 Pointer=(nil) Extent=(nil) Flags=I/-/-/-/-/-/E
                  FreedLocation=0 Alloc=0.000000 Size=0.000000 LoadTime=0
                Block:  #='6' name=SQLP^cb04d3eb pins=0 Change=NONE   
                  Heap=0x7870b820 Pointer=0x7fe88d18 Extent=0x7fe88130 Flags=I/-/P/A/-/-/E
                  FreedLocation=0 Alloc=13.750000 Size=16.070312 LoadTime=0
                Block:  #='7' name=parameters pins=0 Change=NONE   
                  Heap=0x7fe886e0 Pointer=0x7cdfef50 Extent=(nil) Flags=I/-/-/A/-/-/E
                  FreedLocation=0 Alloc=0.000000 Size=0.000000 LoadTime=0
                Block:  #='13' name=repository pins=0 Change=NONE   
                  Heap=0x7fe88640 Pointer=(nil) Extent=(nil) Flags=I/-/-/-/-/-/E
                  FreedLocation=0 Alloc=0.000000 Size=0.000000 LoadTime=0
                Block:  #='14' name=errors pins=0 Change=NONE   
                  Heap=0x7fe88820 Pointer=(nil) Extent=(nil) Flags=I/-/-/-/-/-/E
                  FreedLocation=0 Alloc=0.000000 Size=0.000000 LoadTime=0
            NamespaceDump:  
              Child Cursor:  Heap0=0x6d4490e0 Heap6=0x7fe88d18 Heap0 Load Time=06-08-2017 11:49:00 Heap6 Load Time=06-08-2017 11:49:00
    NamespaceDump:  
      Parent Cursor:  sql_id=24hu3335h9nzb parent=0x7870b1c8 maxchild=1 plk=y ppn=n prsfcnt=0 obscnt=0

Name=CDB$ROOT.SYS.LOCK_TEST
LockMode=N PinMode=S LoadLockMode=0


session B: 68
SQL> alter procedure lock_test compile; hang

Bucket: #=116413 Mutex=0x825d4e40(111669149696, 7122, 0, 6)
  LibraryHandle:  Address=0x6a6e6500 Hash=e487c6bdLockMode=X PinMode=S LoadLockMode=0 Status=VALD
    ObjectName:  Name=CDB$ROOT.SYS.LOCK_TEST  
      FullHashValue=93d3eb836a7cc834b1ad6ebee487c6bd Namespace=TABLE/PROCEDURE(01) Type=PROCEDURE(07) ContainerId=1 ContainerUid=1 Identifier=88559 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=2 TotalLockCount=9 TotalPinCount=4
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=75 HandleInUse=75 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x6a6e65b0(0, 8, 0, 0) Mutex=0x6a6e6650(26, 7674, 0, 6)
    Flags=PIN/TIM/[00002801] Flags2=[0000]
    WaitersLists:  
      Lock=0x6a6e6590[0x6a6e6590,0x6a6e6590]
      Pin=0x6a6e6570[0x7b3e8960,0x7b3e8960]
      LoadLock=0x6a6e65e8[0x6a6e65e8,0x6a6e65e8]
    Timestamp:  Current=06-08-2017 11:45:39
    HandleReference:  Address=0x6a6e66d8 Handle=0x808a3310 Flags=OWN[200]
    ReferenceList:  
      Reference:  Address=0x80272eb0 Handle=0x78cc8d40 Flags=DEP[01]
        Timestamp=06-08-2017 11:45:39 InvalidatedFrom=0
    LibraryObject:  Address=0x7df0e260 HeapMask=0000-0115-0115-0000 Flags=EXS/LOC[0004] Flags2=[8000000] PublicFlags=NST[0001]
      Dependencies:  count='3' size='16' table='0x7df0f108'
        Dependency:  num='0'
          Reference=0x7df0e4c8 Position=0 Flags=DEP[0001]
          Handle=0x7efe5508 Type=PACKAGE(09) Parent=CDB$ROOT.SYS.STANDARD   
        Dependency:  num='1'
          Reference=0x7df0e508 Position=0 Flags=DEP[0001]
          Handle=0x7def8f30 Type=PACKAGE(09) Parent=CDB$ROOT.SYS.DBMS_LOCK   
        Dependency:  num='2'
          Reference=0x7df0e570 Position=0 Flags=DEP[0001]
          Handle=0x6550d228 Type=PACKAGE(09) Parent=CDB$ROOT.SYS.SYS_STUB_FOR_PURITY_ANALYSIS   
      Accesses:  count='1' size='16'
        Dependency:  num='1' Type=000c
      DataBlocks:  
        Block:  #='0' name=KGLH0^e487c6bd pins=0 Change=NONE   
          Heap=0x7dcba6f8 Pointer=0x7df0e380 Extent=0x7df0e1b8 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=1.812500 Size=3.976562 LoadTime=611979361
        Block:  #='2' name=PLDIA^e487c6bd pins=0 Change=NONE   
          Heap=0x7df0e610 Pointer=0x7f28be78 Extent=0x7f28bdd0 Flags=I/-/-/A/-/-/-
          FreedLocation=0 Alloc=13.148438 Size=16.000000 LoadTime=611979361
        Block:  #='4' name=PLMCD^e487c6bd pins=1 Change=NONE   
          Heap=0x7df0e6e8 Pointer=0x7f285df8 Extent=0x7f285d50 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=1.078125 Size=4.000000 LoadTime=611979363
        Block:  #='8' name=KGLS^e487c6bd pins=1 Change=NONE   
          Heap=0x7df0e7c0 Pointer=0x7f285c98 Extent=0x7f284d50 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=0.156250 Size=4.000000 LoadTime=611979365


Bucket: #=47427 Mutex=0x82333330(171798691840, 83, 0, 6)
  LibraryHandle:  Address=0x6a6fa358 Hash=6cd8b943 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=alter procedure lock_test compile
      FullHashValue=38ffbdb7effa19a770d911286cd8b943 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1 ContainerUid=1 Identifier=1826142531 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=1 TotalLockCount=2 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x6a6fa408(0, 1, 0, 0) Mutex=0x6a6fa4a8(40, 23, 0, 6)
    Flags=RON/PIN/TIM/PN0/DBN/[10012841] Flags2=[0000]
    WaitersLists:  
      Lock=0x6a6fa3e8[0x6a6fa3e8,0x6a6fa3e8]
      Pin=0x6a6fa3c8[0x6a6fa3c8,0x6a6fa3c8]
      LoadLock=0x6a6fa440[0x6a6fa440,0x6a6fa440]
    Timestamp:  Current=06-08-2017 11:58:48
    HandleReference:  Address=0x6a6fa540 Handle=(nil) Flags=[00]
    ReferenceList:  
      Reference:  Address=0x6ab61ff8 Handle=0x7db0c600 Flags=ROD[21]
    LibraryObject:  Address=0x7b289d30 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
      DataBlocks:  
        Block:  #='0' name=KGLH0^6cd8b943 pins=0 Change=NONE   
          Heap=0x752cab58 Pointer=0x7b289e00 Extent=0x7b289c88 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=2.539062 Size=3.976562 LoadTime=610176104
      ChildTable:  size='16'
        Child:  id='0' Table=0x7b28abb0 Reference=0x7b28a678 Handle=0x7886c0d0
      Children:  
        Child:  childNum='0'
          LibraryHandle:  Address=0x7886c0d0 Hash=0 LockMode=N PinMode=X LoadLockMode=0 Status=VALD
            Name:  Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1
            Statistics:  InvalidationCount=0 ExecutionCount=1 LoadCount=1 ActiveLocks=1 TotalLockCount=1 TotalPinCount=2
            Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
            Concurrency:  DependencyMutex=0x7886c180(0, 0, 0, 0) Mutex=0x6a6fa4a8(40, 23, 0, 6)
            Flags=RON/PIN/PN0/EXP/CHD/[10012111] Flags2=[0000]
            WaitersLists:  
              Lock=0x7886c160[0x7886c160,0x7886c160]
              Pin=0x7886c140[0x7886c140,0x7886c140]
              LoadLock=0x7886c1b8[0x7886c1b8,0x7886c1b8]
            ReferenceList:  
              Reference:  Address=0x7b28a678 Handle=0x6a6fa358 Flags=CHL[02]
            LibraryObject:  Address=0x65e23d38 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
              ReadOnlyDependencies:  count='1' size='16'
                ReadDependency:  num='0' Table=0x65e24be0 Reference=0x65e24418 Handle=0x7db0c600 HandleFlag=0x12805 RefFlags=DEP/ROD/KPP[61]
              DataBlocks:  
                Block:  #='0' name=KGLH0^6cd8b943 pins=0 Change=NONE   
                  Heap=0x7ef37780 Pointer=0x65e23e08 Extent=0x65e23c90 Flags=I/-/P/A/-/-/-
                  FreedLocation=0 Alloc=2.132812 Size=3.937500 LoadTime=610176104
                Block:  #='6' name=SQLA^6cd8b943 pins=0 Change=NONE   
                  Heap=0x7b28a458 Pointer=0x7cf9ac18 Extent=0x7cf9a030 Flags=I/-/P/A/-/-/E
                  FreedLocation=0 Alloc=4.289062 Size=7.898438 LoadTime=0
            NamespaceDump:  
              Child Cursor:  Heap0=0x65e23e08 Heap6=0x7cf9ac18 Heap0 Load Time=06-08-2017 11:58:48 Heap6 Load Time=06-08-2017 11:58:48
    NamespaceDump:  
      Parent Cursor:  sql_id=71q8j51qdjfa3 parent=0x7b289e00 maxchild=1 plk=y ppn=n prsfcnt=0 obscnt=0


Name=CDB$ROOT.SYS.LOCK_TEST
64持有:
LockMode=N PinMode=S LoadLockMode=0
68请求:
LockMode=X PinMode=X (trace 中X 没有成功仍然是S 模式)

64在parent上持有N模式,68在parent上请求X模式可以获取
64在child上持有S模式,68在child 上请求X模式等待



   SID EVENT
------ ----------------------------
    43 SQL*Net message from client
    64 PL/SQL lock timer
    68 library cache pin


session C:43
SQL> drop procedure lock_test;--hang
Bucket: #=116413 Mutex=0x825d4e40(111669149696, 7343, 0, 6)
  LibraryHandle:  Address=0x6a6e6500 Hash=e487c6bd LockMode=X PinMode=S LoadLockMode=0 Status=VALD
    ObjectName:  Name=CDB$ROOT.SYS.LOCK_TEST   
      FullHashValue=93d3eb836a7cc834b1ad6ebee487c6bd Namespace=TABLE/PROCEDURE(01) Type=PROCEDURE(07) ContainerId=1 ContainerUid=1 Identifier=88559 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=2 TotalLockCount=10 TotalPinCount=4
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=78 HandleInUse=78 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x6a6e65b0(0, 9, 0, 0) Mutex=0x6a6e6650(26, 7941, 0, 6)
    Flags=PIN/TIM/[00002801] Flags2=[0000]
    WaitersLists:  
      Lock=0x6a6e6590[0x6a29afb8,0x6a29afb8]
      Pin=0x6a6e6570[0x7b3e8960,0x7b3e8960]
      LoadLock=0x6a6e65e8[0x6a6e65e8,0x6a6e65e8]
    Timestamp:  Current=06-08-2017 11:45:39
    HandleReference:  Address=0x6a6e66d8 Handle=0x808a3310 Flags=OWN[200]
    ReferenceList:  
      Reference:  Address=0x80272eb0 Handle=0x78cc8d40 Flags=DEP[01]
        Timestamp=06-08-2017 11:45:39 InvalidatedFrom=0
    LibraryObject:  Address=0x7df0e260 HeapMask=0000-0115-0115-0000 Flags=EXS/LOC[0004] Flags2=[8000000] PublicFlags=NST[0001]
      Dependencies:  count='3' size='16' table='0x7df0f108'
        Dependency:  num='0'
          Reference=0x7df0e4c8 Position=0 Flags=DEP[0001]
          Handle=0x7efe5508 Type=PACKAGE(09) Parent=CDB$ROOT.SYS.STANDARD   
        Dependency:  num='1'
          Reference=0x7df0e508 Position=0 Flags=DEP[0001]
          Handle=0x7def8f30 Type=PACKAGE(09) Parent=CDB$ROOT.SYS.DBMS_LOCK   
        Dependency:  num='2'
          Reference=0x7df0e570 Position=0 Flags=DEP[0001]
          Handle=0x6550d228 Type=PACKAGE(09) Parent=CDB$ROOT.SYS.SYS_STUB_FOR_PURITY_ANALYSIS   
      Accesses:  count='1' size='16'
        Dependency:  num='1' Type=000c
      DataBlocks:  
        Block:  #='0' name=KGLH0^e487c6bd pins=0 Change=NONE   
          Heap=0x7dcba6f8 Pointer=0x7df0e380 Extent=0x7df0e1b8 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=1.812500 Size=3.976562 LoadTime=611979361
        Block:  #='2' name=PLDIA^e487c6bd pins=0 Change=NONE   
          Heap=0x7df0e610 Pointer=0x7f28be78 Extent=0x7f28bdd0 Flags=I/-/-/A/-/-/-
          FreedLocation=0 Alloc=13.148438 Size=16.000000 LoadTime=611979361
        Block:  #='4' name=PLMCD^e487c6bd pins=1 Change=NONE   
          Heap=0x7df0e6e8 Pointer=0x7f285df8 Extent=0x7f285d50 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=1.078125 Size=4.000000 LoadTime=611979363
        Block:  #='8' name=KGLS^e487c6bd pins=1 Change=NONE   
          Heap=0x7df0e7c0 Pointer=0x7f285c98 Extent=0x7f284d50 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=0.156250 Size=4.000000 LoadTime=611979365


Name=CDB$ROOT.SYS.LOCK_TEST
64持有:
LockMode=N PinMode=S LoadLockMode=0
68持有:LockMode=X,请求 PinMode=X(等待library cache pin)

43请求:
LockMode=X (等待library cache lock)PinMode=X


trace 文件没有drop procedure lock_test信息,也就是没有分配存储过程的游标handle

------ ----------------------------
    43 library cache lock
    64 PL/SQL lock timer
    68 library cache pin


SQL> select sid,saddr from v$session where event='library cache lock';

   SID SADDR
------ ----------------
    43 00000000892D85B8

select kgllkhdl Handle, kgllkreq Request,kglnaobj Object
     from x$kgllk
     where kgllkses = '00000000892D85B8'
     and kgllkreq > 0;

HANDLE              REQUEST OBJECT
---------------- ---------- -----------------------------
000000006A6E6500          3 LOCK_TEST


select kgllkses saddr, kgllkhdl handle,kgllkmod mod, kglnaobj object
      from x$kgllk lock_a
     where kgllkmod > 0
       and exists (select lock_b.kgllkhdl
              from x$kgllk lock_b
             where kgllkses = '00000000892D85B8'/* blocked session*/
               and lock_a.kgllkhdl =lock_b.kgllkhdl
               and kgllkreq > 0);

SADDR            HANDLE                  MOD OBJECT
---------------- ---------------- ---------- -----------------
00000000892A6DA0 000000006A6E6500          1 LOCK_TEST
000000008929D6C0 000000006A6E6500          3 LOCK_TEST

SQL> col object for a30
SQL> /

SADDR            HANDLE                  MOD OBJECT
---------------- ---------------- ---------- ------------------------------
00000000892A6DA0 000000006A6E6500          1 LOCK_TEST
000000008929D6C0 000000006A6E6500          3 LOCK_TEST


select sid, username,event,blocking_session,p1,p2,p3
  from v$session
where saddr in
       (select kgllkses
          from x$kgllk lock_a
         where kgllkreq > 0
           and exists (select lock_b.kgllkhdl
                  from x$kgllk lock_b
                 where kgllkses in( '00000000892A6DA0','000000008929D6C0')/* blocking session*/
                   and lock_a.kgllkhdl =lock_b.kgllkhdl
                   and kgllkreq = 0));

   SID USERNAME     EVENT                        BLOCKING_SESSION         P1         P2         P3
------ ------------ ---------------------------- ---------------- ---------- ---------- ----------
    43 SYS          library cache lock                         68 1785619712 1781116728 3.8036E+14
       ------------

SQL> select to_char(1785619712,'xxxxxxxxxx') from dual;

TO_CHAR(1785619712,'XXXXXXXXXX')
6a6e6500----------正是上面的 LibraryHandle:  Address=0x6a6e6500 地址!


模拟修改表结构:

SESSION A:
    declare
    stra varchar2(200);
    begin
    stra:='alter table mac_kks modify (t2 char(2000))';
    for i in 1..100000000 loop
    execute immediate stra;
    end loop;
    end;
SQL> ALTER SESSION SET EVENTS 'immediate trace name library_cache level 16';

Bucket: #=103327 Mutex=0x82555190(111669149696, 2600778, 71, 6)
  LibraryHandle:  Address=0x7834aad8 Hash=74bb939f LockMode=X PinMode=X LoadLockMode=0 Status=VALD
    ObjectName:  Name=CDB$ROOT.SYS.MAC_KKS  
      FullHashValue=2f3e6534ce44a52ad7a34e0c74bb939f Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) ContainerId=1 ContainerUid=1 Identifier=77071 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=6695 ActiveLocks=1 TotalLockCount=46866 TotalPinCount=46865
    Counters:  BrokenCount=6696 RevocablePointer=6695 KeepDependency=0 Version=0 BucketInUse=40169 HandleInUse=40169 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x7834ab88(0, 40117, 0, 0) Mutex=0x7834ac28(26, 388467, 2, 6)
    Flags=PIN/TIM/PTM/FUP/[08003801] Flags2=[0000]
    WaitersLists:  
      Lock=0x7834ab68[0x7834ab68,0x7834ab68]
      Pin=0x7834ab48[0x7834ab48,0x7834ab48]
      LoadLock=0x7834abc0[0x7834abc0,0x7834abc0]
    Timestamp:  Current=06-08-2017 13:07:44 Previous=06-08-2017 13:07:43
    HandleReference:  Address=0x7834acb0 Handle=0x808a3310 Flags=OWN[200]
    ReferenceList:  
      Reference:  Address=0x783488e0 Handle=0x78349050 Flags=DEP[01]
        Timestamp=06-08-2017 13:07:43 InvalidatedFrom=0
    LibraryObject:  Address=0x78345ad8 HeapMask=0000-0701-0701-0000 Flags=EXS/LOC/PRG[0084] Flags2=[8000000] PublicFlags=[0000]
      DataBlocks:  
        Block:  #='0' name=KGLH0^74bb939f pins=0 Change=NONE   
          Heap=0x78347e90 Pointer=0x78345ba8 Extent=0x78345a30 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=1.492188 Size=3.976562 LoadTime=614310657
        Block:  #='8' name=KGLS^74bb939f pins=1 Change=NONE   
          Heap=0x78345e70 Pointer=0x7a545b58 Extent=0x7a544e78 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=21.257812 Size=23.859375 LoadTime=614310657
        Block:  #='9' name=KGLS^74bb939f pins=1 Change=NONE   
          Heap=0x78345f98 Pointer=0x7a4febb8 Extent=0x7a4fdd88 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=0.429688 Size=4.000000 LoadTime=614310666
        Block:  #='10' name=KGLS^74bb939f pins=1 Change=NONE   
          Heap=0x78346020 Pointer=0x7a4fdc98 Extent=0x7a4fcd88 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=0.210938 Size=4.000000 LoadTime=614310666

DDL操作添加:LockMode=X PinMode=X



思路整理:
ibrary cache lock l 和 library cache pin 操作的模拟也就完成了。我们需要明确理解 ibrary cache lock l 和 library cache pin操作的意思,当遇到该等待事件时能做到心中有数,知道oracle 在操作什么,
等待什么,这样才能快速处理问题。
mutex 或者之前的latch 是用来保护 ibrary cache lock l 和 library cache pin 操作的!




论坛徽章:
5
娜美
日期:2017-06-26 15:18:15目光如炬
日期:2017-08-20 22:00:00目光如炬
日期:2017-08-27 22:00:01火眼金睛
日期:2017-09-01 17:00:07目光如炬
日期:2017-09-03 22:00:01
 楼主| 发表于 2017-8-18 13:45 | 显示全部楼层
本帖最后由 sunyunyi 于 2017-8-18 13:46 编辑

给大家一个查询 library cache lock 和 liarary cache  pin 的SQL 语句:

library cache lock:

select kgllkses,kgllkhdl,kgllkmod,kgllkreq
from x$kgllk lock_a
where kgllkmod  > 1
union
select kgllkses,kgllkhdl,kgllkmod,kgllkreq
from x$kgllk lock_b
where kgllkreq  > 1


select sid, username,event,blocking_session
from v$session
where saddr in (
select kgllkses
from
(
select kgllkses,kgllkhdl,kgllkmod,kgllkreq
from x$kgllk lock_a
where kgllkmod  > 1
union
select kgllkses,kgllkhdl,kgllkmod,kgllkreq
from x$kgllk lock_b
where kgllkreq  > 1
)
)
/

liarary cache  pin:

select KGLPNSES,KGLPNHDL,KGLPNMOD,KGLPNREQ
from x$kglpn a
where a.KGLPNMOD >1
union
select KGLPNSES,KGLPNHDL,KGLPNMOD,KGLPNREQ
from x$kglpn b
where b.KGLPNREQ >1


select sid, username,event,blocking_session
from v$session
where saddr in (
select KGLPNSES
from
(
select KGLPNSES,KGLPNHDL,KGLPNMOD,KGLPNREQ
from x$kglpn a
where a.KGLPNMOD >1
union
select KGLPNSES,KGLPNHDL,KGLPNMOD,KGLPNREQ
from x$kglpn b
where b.KGLPNREQ >1
)
)
/

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

SACC2017购票8.8折优惠进行时

2017中国系统架构师大会(SACC2017)将于10月19-21日在北京新云南皇冠假日酒店震撼来袭。今年,大会以“云智未来”为主题,云集国内外顶级专家,围绕云计算、人工智能、大数据、移动互联网、产业应用等热点领域展开技术探讨与交流。本届大会共设置2大主会场,18个技术专场;邀请来自互联网、金融、制造业、电商等多个领域,100余位技术专家及行业领袖来分享他们的经验;并将吸引4000+人次的系统运维、架构师及IT决策人士参会,为他们提供最具价值的交流平台。
----------------------------------------
优惠时间:2017年8月30日前

活动链接>>
TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表