Library cache結構與Library cache lock、Library cache pin等待事件

一、Library cache 結構

1.DSI中對Library Cache的說明：

（1）An area in the shared pool that manages information about:

         共享池中的一塊區域，負責存儲如下對象的信息：

   --Shared cursors (SQL and PL/SQL objects)  共享游標(sql和pl/sql對象)

   --Database objects (tables, indexes, and so on)  數據庫對象(表，索引等)

（2）Initially created to manage PL/SQL programs and library units, therefore called library cache

          最初被創建用來管理PL/SQL程序和庫單元,因此稱為庫緩存

（3）Scope was extended to include shared cursors and information about other RDBMS objects.

          范圍擴展到包括共享游標和其他RDBMS對象的信息

2.Library Cache Objects(庫緩存對象)

（1）The units of information that are stored in the library cache are called objects.

          存儲在庫緩存中的信息單元稱為對象

（2）There are two classes of objects:

          兩種類型的對象：

  1）Stored objects 存儲對象(永久對象)

   --Created and dropped with explicit SQL or PL/SQL commands。 使用顯式SQL或PL/SQL命令創建和刪除的對象

   Examples: Tables, views, packages,functions  如表、視圖、包、函數等。

  2）Transient objects 瞬時對象(非永久對象)

   --Created at execution time and live only for the duration of the instance(or aged out)  在執行時創建，并且僅在實例的生命周期內存在或消失

   Example: Shared and nonshared cursors   如共享或非共享游標

3.Shared Cursors

（1）In-memory representation of an executable object: 可執行對象在內存中的表示

   SQL statements SQL語句

   Anonymous PL/SQL block 匿名PL/SQL塊

   PL/SQL stored procedures or functions  存儲過程或函數

（2）Represented by two or more objects: 含有 兩個或兩個以上的對象

   A parent cursor that has a name 父游標

   One or more child cursors containing the execution plan 子游標存儲執行計劃

4.Library Cache Architecture

（1）The library cache is a hash table that is accessible through an array of hash buckets.

         庫緩存是一個由多個hash bucket構成的hash table

（2）The library cache manager(KGL) controls the access and usage of library cache objects.

          庫緩存管理器(KGL)控制庫緩存對象的訪問和使用。

（3）Memory for the library cache is allocated from the shared pool.

         庫緩存的存儲空間從共享池中分配

5. Library cache需要解決三個問題：

（1）快速定位的問題：Library cache中對象眾多，Oracle如何管理這些對象，以便服務進程可以迅速找到他們需要的信息。比如某個服務進程需要迅速定位某個SQL是否存在于Library cache中。

（2）關系依賴的問題：Library cache中的對象存在復雜的依賴關系，當某個objec失效時，可以迅速將依賴其的對象也置為失效狀態。比如某個表發生了結構變化，依賴其的SQL語句需要重新解析。

（3）并發控制的問題：Library cache中必須有一個并發控制的機構，比如鎖機制，來管理大量共享對象的并發訪問和修改的問題，比如某個SQL在重新編譯的同時，其所依賴的對象不能被修改。

Oracle利用hash table結構來解決library cache中快速定位的問題，hash table就是很多hash bucket組成的數組。

先看幾張相關的圖片：

Library Cache保存了explicit SQL、PL/SQL commands、shared and nonshared cursors。這些對象都保存在Hash table里，Hash table又由Hash Bucket組成。Hash Bucket 由一些Object Handle List 組成，所以在Hash Bucket里查找某個對象，就是搜索這個Handle List。

6. Object Handle

在上圖我們可以看到Object handle保存的信息。Library cache object handle指向library cache object(LCO, heap 0),它包含了library cache object的名字、命名空間、時間戳、引用列表、lock對象以及pin對象的列表信息等等。

所以對Library cache中所有對象的訪問是通過利用library cache object handle來實現的，也就是說我們想要訪問library cache object，我們必須先找到 library cache object handle 。 因為Object handle保存了lock和pin的信息，即記錄哪個用戶在這個handle上有lock，或者是哪個用戶正在等待獲得這個lock。因此library cache lock是發生在handle上的。 當一個進程請求library cache object, library cache manager就會應用一個hash算法，從而得到一個hash值，根據相應的hash值到相應的hash bucket中去尋找。 如果library cache object在內存中，那么這個 library cache object handle 就會被找到。有時候，當shared pool不夠大， library cache object handle 會保留在內存中，然而library cache heap由于內存不足被age out，這個時候我們請求的object heap就會被重載( 硬解析 )。最壞的情況下， library cache object handle 在內存中沒有找到，這個時候就必須分配一個新的 library cache object handle ，同時object heap也會被加載到內存中( 硬解析 )。

7. Library Cache Object(LCO: Heap 0)

它的結構信息如下圖：

DSI的說明：

  (1) Internally, most of the object identity is represented by structures of type kglob.

  (2) These are the structures stored in heap 0.

  (3) Object structures have the following components:

    Type

    Name

    Flags

    Tables

    Datablocks

  Library Cache 存儲SQL或者shared cursors 等。 這些信息就是通過Heap 0 這個LCO 來保存的。

7.1 Object Types

  (1) Objects are grouped in namespaces according to their type.

  (2) Each object can only be of one type.

  (3) All the objects of the same type are in the same namespace.    

  (4) A namespace may be used by more than one type.

  (5) The most important namespace is called cursor(CRSR) and houses the shared SQL cursors.

7.2 Object Names

  (1) Library cache object names have three parts:

    Name of schema

    Name of object

    Name of database link (remote objects only)

  (2) The format used is SCHEMA.NAME@DBLINK.

    For example: HR.EMPLOYEES@ACME.COM

7.3 Object Flags

  （1）Public flags:

    Are not protected by pins or latches

    Indicate in detail the type of the object

  （2）Status flags:

    Are protected by pins

    Indicate whether the object is being created/dropped/altered/updated

  （3）Special status flags:

    Are protected by the library cache latch

    Are related to object validity and authorization

7.4 Object Tables

（1）Dependency table

（2）Child table

（3）Translation table

（4）Authorization table

（5）Access table

（6）Read-only dependency table

（7）Schema name table

   7.4.1 dependency table

   指向本對象所依賴的對象，比如：select * from emp這個cursor的對象，依賴emp這個表，這里指向了emp這個表的handle。

   7.4.2.child table

   指向本對象的子對象，比如某個游標的子游標。通俗點說，就是一條SQL至少有一個parent cursor 和 child cursor。可能一些SQL由于某些原因無法共享child cursor，這樣就會出現一個parent cursor和多個child cursor的情況。即version count很高。那么這種情況下，parent cursor里對應的所有child cursor的指針都會保存在child table里面。 Oracle是用C寫的，所以這里是指針。

   注意一點，parent cursor和child cursor都是用library cache object handle 存儲在Library Cache里的。即他們的結構完全一樣。這個結論可以通過library cache的dump 文件來證明。在后面我們會做一個測試。

   7.4.3.authorization table

   對象的授權信息。

7.5 Object Data Blocks

（1）The remainder of an object's data is stored in other independent data heaps.

（2）The object structure contains an array of data block structures.

（3）The data block structures have a pointer to a different data heap.

（4）An object structure has room for 16 data block structures but not all of them are in use.

  Heap0 也僅僅保存是一個結構，它不保存實際的data。而實際存儲data的Heap的指針就存放在這個Data Blocks里。這個也可以通過dump來查看。這個Data Blocks指向的Heap結構如下圖：

  這里要注意的，就是 我們SQL的執行計劃就是存放在這個Heap 6：SQL Context 中 。

二、dump出library cache，進行觀察

  首先在一個父游標上生成兩個子游標，sys用戶和scott用戶下分別創建tb_test表，并執行語句：select object_name from tb_test where object_id = 20;

SQL> conn scott/tiger

Connected.

SQL> create table tb_test as select * from dba_objects where object_id < 100;

Table created.

SQL> select object_name from tb_test where object_id = 20;

OBJECT_NAME

---------------------------------------------

ICOL$

SQL> show user  

USER is "SYS"

SQL> create table tb_test as select * from dba_objects where object_id < 100;

Table created.

SQL> select object_name from tb_test where object_id = 20;

OBJECT_NAME

----------------------------------------------

ICOL$

SQL> select sql_id,version_count from v$sqlarea where sql_text like 'select object_name from tb_test where object_id%';

SQL_ID        VERSION_COUNT

------------- -------------

5tq4nhdw908dy             2

SQL> select address,child_address,child_number from v$sql where sql_id='5tq4nhdw908dy';

ADDRESS          CHILD_ADDRESS    CHILD_NUMBER

---------------- ---------------- ------------

00000000620A5990 00000000620A5510            0

00000000620A5990 0000000062290028            1

此時可看到， 在父游標下已經生成了兩個子游標 。

將library cache dump出來：

SQL> oradebug setmypid

Statement processed.

SQL> oradebug dump library_cache 16

Statement processed.

SQL> oradebug tracefile_name

/opt/app/oracle/diag/rdbms/bddev/BDDEV/trace/BDDEV_ora_54420.trc

在trace文件中查找父游標 0x620A5990 :

Bucket: #=8638 Mutex=0x69ac5508(0, 39, 0, 6)

     --object handle的內存地址

  LibraryHandle:  Address= 0x620a5990 Hash=789021be LockMode=N PinMode=0 LoadLockMode=0 Status=VALD

       --object的名稱

    ObjectName:  Name=select object_name from tb_test where object_id = 20

      --hash值與Namespace、Type

      FullHashValue=239de4faa6a0ef3f5cd89483789021be Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=2022711742 OwnerIdn=0

    Statistics:  InvalidationCount=0 ExecutionCount=2 LoadCount=3 ActiveLocks=2 TotalLockCount=2 TotalPinCount=1

    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=2 Version=0 BucketInUse=1 HandleInUse=1 HandleReferenceCount=0

    Concurrency:  DependencyMutex=0x620a5a40(0, 6, 0, 0) Mutex=0x620a5ad0(48, 119, 0, 6)

     --flag信息

    Flags=RON/PIN/TIM/PN0/DBN/[10012841]

     --lock、pin信息

    WaitersLists:  

      Lock=0x620a5a20[0x620a5a20,0x620a5a20]

      Pin=0x620a5a00[0x620a5a00,0x620a5a00]

      LoadLock=0x620a5a78[0x620a5a78,0x620a5a78]

    Timestamp:  Current=08-03-2018 16:43:25

    HandleReference:  Address=0x620a5b70 Handle=(nil) Flags=[00]

    ReferenceList:  

      Reference:  Address=0x6514e3c0 Handle=0x6228fd38 Flags=ROD[21]

      Reference:  Address=0x6521b3c0 Handle=0x620a5220 Flags=ROD[21]

    LibraryObject:  Address=0x6521d0b0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]

      DataBlocks:  

        Block:  #='0' name=KGLH0^789021be pins=0 Change=NONE   

          Heap=0x620a58d8 Pointer=0x6521d150 Extent=0x6521d030 Flags=I/-/P/A/-/-

          FreedLocation=0 Alloc=3.289062 Size=3.976562 LoadTime=5081449990

      ChildTable:  size='16'

           --兩個子游標

        Child:  id='0' Table=0x6521df60 Reference=0x6521d9b8 Handle= 0x620a5510

        Child:  id='1' Table=0x6521df60 Reference=0x6521dd00 Handle= 0x62290028

      Children:  

        Child:  childNum='0'

          LibraryHandle:  Address= 0x620a5510 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD

            Name:  Namespace=SQL AREA(00) Type=CURSOR(00)

            Statistics:  InvalidationCount=0 ExecutionCount=1 LoadCount=1 ActiveLocks=1 TotalLockCount=2 TotalPinCount=3

            Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0

            Concurrency:  DependencyMutex=0x620a55c0(0, 0, 0, 0) Mutex=0x620a5ad0(48, 119, 0, 6)

            Flags=RON/PIN/PN0/EXP/CHD/[10012111]

            WaitersLists:  

              Lock=0x620a55a0[0x620a55a0,0x620a55a0]

              Pin=0x620a5580[0x620a5580,0x620a5580]

              LoadLock=0x620a55f8[0x620a55f8,0x620a55f8]

            ReferenceList:  

              Reference:  Address=0x6521d9b8 Handle=0x620a5990 Flags=CHL[02]

            LibraryObject:  Address=0x6521c0b0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]

              Dependencies:  count='1' size='16' table='0x6521cee8'

                --子游標依賴信息

                Dependency:  num='0'

                  Reference=0x6521c670 Position=24 Flags=DEP[0001]

                  Handle=0x6233e978 Type=TABLE(02) Parent= SYS.TB_TEST    

              ReadOnlyDependencies:  count='1' size='16'

                ReadDependency:  num='0' Table=0x6521cf80 Reference=0x6521c568 Handle=0x620a5220 Flags=DEP/ROD/KPP[61]

              Accesses:  count='1' size='16'

                Dependency:  num='0' Type=0009

              Translations:  count='1' size='16'

                Translation:  num='0' Original=0x6233e978 Final=0x6233e978

              DataBlocks:  

                Block:  #='0' name=KGLH0^789021be pins=0 Change=NONE    --Block 0，對應 x$kglob. KGLOBHD0

                  Heap=0x620a5458 Pointer=0x6521c150 Extent=0x6521c030 Flags=I/-/P/A/-/-

                  FreedLocation=0 Alloc=2.273438 Size=3.937500 LoadTime=5081449990

                 --Block6相當于heap6,存放具體執行計劃，對應 x$kglob. KGLOBHD6

                Block:  #='6' name=SQLA^789021be pins=0 Change=NONE   

                  Heap= 0x6521d788 Pointer=0x628c1188 Extent=0x628c0548 Flags=I/-/-/A/-/E

                  FreedLocation=0 Alloc=6.195312 Size=7.898438 LoadTime=0

            NamespaceDump:  

              Child Cursor:  Heap0=0x6521c150 Heap6=0x628c1188 Heap0 Load Time=08-03-2018 16:43:25 Heap6 Load Time=08-03-2018 16:43:25

        Child:  childNum='1'

          LibraryHandle:  Address=0x62290028 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD

            Name:  Namespace=SQL AREA(00) Type=CURSOR(00)

            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=0x622900d8(0, 0, 0, 0) Mutex=0x620a5ad0(48, 119, 0, 6)

            Flags=RON/PIN/PN0/EXP/CHD/[10012111]

            WaitersLists:  

              Lock=0x622900b8[0x622900b8,0x622900b8]

              Pin=0x62290098[0x62290098,0x62290098]

              LoadLock=0x62290110[0x62290110,0x62290110]

            ReferenceList:  

              Reference:  Address=0x6521dd00 Handle=0x620a5990 Flags=CHL[02]

            LibraryObject:  Address=0x6514f0b0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]

              Dependencies:  count='2' size='16' table='0x6514fee8'

                Dependency:  num='0'

                  Reference=0x6514f668 Position=0 Flags=DEP[0001]

                  Handle=0x620ad0e8 Type=NONE(255) Parent=SCOTT   

                Dependency:  num='1'

                  Reference=0x6514f6b8 Position=24 Flags=DEP[0001]

                  Handle=0x62fa4d00 Type=TABLE(02) Parent=SCOTT.TB_TEST   

              ReadOnlyDependencies:  count='1' size='16'

                ReadDependency:  num='0' Table=0x6514ff80 Reference=0x6514f568 Handle=0x6228fd38 Flags=DEP/ROD/KPP[61]

              Accesses:  count='1' size='16'

                Dependency:  num='1' Type=0009

              Translations:  count='1' size='16'

                Translation:  num='0' Original=0x62fa4d00 Final=0x62fa4d00

              DataBlocks:  

                Block:  #='0' name=KGLH0^789021be pins=0 Change=NONE   

                  Heap=0x6228ff70 Pointer=0x6514f150 Extent=0x6514f030 Flags=I/-/P/A/-/-

                  FreedLocation=0 Alloc=2.343750 Size=3.937500 LoadTime=5081489580

                Block:  #='6' name=SQLA^789021be pins=0 Change=NONE   

                  Heap=0x6521dba0 Pointer=0x619d3c70 Extent=0x619d3030 Flags=I/-/-/A/-/E

                  FreedLocation=0 Alloc=6.195312 Size=7.898438 LoadTime=0

            NamespaceDump:  

              Child Cursor:  Heap0=0x6514f150 Heap6=0x619d3c70 Heap0 Load Time=08-03-2018 16:44:04 Heap6 Load Time=08-03-2018 16:44:04

    NamespaceDump:  

      Parent Cursor:  sql_id=5tq4nhdw908dy parent=0x6521d150 maxchild=2 plk=y ppn=n

        CursorDiagnosticsNodes:  

          ChildNode:  ChildNumber=0 ID=37 reason=Authorization Check failed(4) size=5x4 translation_table_position=0 original_handle=1660570880 temp_handle=1647569272 schema=83 synonym_object_number=0

把子游標0x620a5510的heap6 dump出來看下：

SQL> alter session set events 'immediate trace name heapdump_addr level 2,addr 0x6521d788';

Session altered.

HEAP DUMP heap name="SQLA^789021be"  desc= 0x6521d788

extent sz=0xfe8 alt=32767 het=368 rec=0 flg=2 opc=2

parent=0x60001190 owner=0x6521d648 nex=(nil) xsz=0xfe8 heap=(nil)

fl2=0x27, nex=(nil), dsxvers=1, dsxflg=0x0

dsx first ext=0x628c0548

EXTENT 0 addr=0x628bc530

  Chunk        0628bc540 sz=     1664    free      "               "

Dump of memory from 0x00000000628BC540 to 0x00000000628BCBC0

0628BC540 00000681 C0B38F00 00000000 00000000  [................]

0628BC550 6521D848 00000000 6521D848 00000000  [H.!e....H.!e....]

0628BC560 00000000 00000000 00000000 00000000  [................]

        Repeat 1 times

0628BC580 000000E1 00B38F00 628BC4A0 00000000  [...........b....]

0628BC590 0A0FD9A8 00000000 00000000 00150015  [................]

0628BC5A0 00020015 00000016 00008100 00000000  [................]

0628BC5B0 00000000 00000000 00000000 00000000  [................]

....

Oracle把sql的執行計劃存在了這個sql的子cursor的heap 6（也就是sql area）中,只不過存儲的形式是編譯好的二進制格式。

三、Library cache lock與Library cache pin

這兩個等待事件有相同的參數：

SQL> select name,parameter1,parameter2,parameter3,wait_class from v$event_name where name in ('library cache lock','library cache pin');

NAME               PARAMETER1     PARAMETER2   PARAMETER3         WAIT_CLASS

------------------ -------------- ------------ ------------------ -----------

library cache pin  handle address pin address  100*mode+namespace Concurrency

library cache lock handle address lock address 100*mode+namespace Concurrency

parameter1:被檢查或加載的對象的地址

parameter2:加載鎖的地址

parameter3:包含模式+命名空間

    Library cache handle 里保存了lock 和 pin 的信息。而且在Library cache handle 和child cursor 上都有lock 和pin。它們稱為library cache lock和library cache pin。

    Library cache lock/pin是用來控制對library cache object的并發訪問的。Lock管理并發，pin管理一致性， lock 是針對于 library cache handle,   而 pin 是針對于 heap。

    當我們想要訪問某個library cache object，我們首先要獲得指向這個object handle的lock，獲得這個lock之后我們就需要pin住指向這個object的heap。

    當我們對包，存儲過程，函數，視圖進行編譯的時候 ，Oracle就會在這些對象的handle上面首先獲得一個library cache lock，然后再在這些對象的heap上獲得pin，這樣就能保證在編譯的時候其它進程不會來更改這些對象的定義，或者將對象刪除。

    當一個 session 對 SQL 語句進行硬解析的時候 ， 這個 session 就必須獲得 librarycache lock ，這樣其他 session 就不能夠訪問或者更改這個 SQL 所引用的對象 。如果這個等待事件花了很長時間，通常表明共享池太小(由于共享池太小，需要搜索free的chunk，或者將某些可以被移出的object page out，這樣要花很長時間)，當然了，也有可能另外的session正在對object進行修改(比如split 分區),而當前session需要引用那個table，那么這種情況下我們必須等另外的session進行完畢。

Library Cache lock 有 3 中模式：

       （1）Share(S):      當讀取一個library cache object的時候獲得

       （2）Exclusive(X):  當創建/修改一個library cache object的時候獲得

       （3）Null(N)：       用來確保對象依賴性

    比如一個進程想要編譯某個視圖，那么就會獲得一個共享鎖；如果我們要create/drop/alter某個對象，那么就會獲得exclusive lock。Null鎖非常特殊，我們在任何可以執行的對象(cursor，function)上面都擁有NULL鎖，我們可以隨時打破這個NULL鎖，當這個NULL鎖被打破了,就表示這個對象被更改了，需要從新編譯。

    NULL 鎖主要的目的就是標記某個對象是否有效 。比如一個SQL語句在解析的時候獲得了NULL 鎖，如果這個SQL的對象一直在共享池中，那么這個NULL鎖就會一直存在下去，當這個SQL語句所引用的表被修改之后，這個NULL鎖就被打破了，因為修改這個SQL語句的時候會獲得Exclusive 鎖，由于NULL鎖被打破了，下次執行這個SQL的時候就需要重新編譯。

    Library Cache pin 有 2 種模式：

       （1）Share(S):      讀取object heap

       （2）Exclusive(X)： 修改object heap

    當某個session想要讀取object heap，就需要獲得一個共享模式的pin，當某個session想要修改object heap，就需要獲得排他的pin。當然了在獲得pin之前必須獲得lock。

    在Oracle10gR2中，library cache pin被library cache mutex所取代。

    Library cache latch用來控制對library cache object的并發訪問。前面已經提到，我們要訪問library cache object之前必須獲得library cache lock， lock不是一個原子操作(原子操作就是在操作程中不會被打破的操作，很明顯這里的lock可以被打破), Oracle為了保護這個lock， 引入了 library cache latch 機制，也就是說在獲得 library cache lock 之前，需要先獲得 library cache latch ，當獲得 library cache lock 之后就釋放 library cache latch 。

    如果某個 library cache object 沒有在內存中，那么這個 lock 就不能被獲取，這個時候需要獲得一個 library cache load lock latch ，然后再獲取一個 library cache load lock, 當 load lock 獲得之后就釋放 library cache load lock latch 。

    library cache latch受隱含參數_KGL_LATCH_COUNT的控制，默認值為大于等于系統中CPU個數的最小素數，但是Oracle對其有一個硬性限制，該參數不能大于67。 注意：我們去查詢_kgl_latch_count有時候顯示為0,這是一個bug。

    Oracle利用下面算法來確定library cache object handle是由哪個子latch來保護的：

       latch#= mod(bucket#, #latches)

    也就是說用哪個子latch去保護某個handle是根據那個handle所在的bucket號，以及總共有多少個子latch來進行hash運算得到的。

四、Library cache lock/pin的測試與查詢

創建存儲過程：

SQL> create or replace procedure calling

  2  is

  3  begin

  4   dbms_lock.sleep(3000);

  5  end;

  6  /

Procedure created.

打開3個session，session1：

SQL> select * from v$mystat where rownum < 2;

       SID STATISTIC#      VALUE

---------- ---------- ----------

         419           0          0

session2：

SQL> select * from v$mystat where rownum < 2;

       SID STATISTIC#      VALUE

---------- ---------- ----------

         773           0          0

session3：

SQL> select * from v$mystat where rownum < 2;

       SID STATISTIC#      VALUE

---------- ---------- ----------

         807           0          0

session1中執行calling：

SQL> exec calling;

session2、3中重新編譯calling：

SQL> alter procedure calling compile;

SQL> select saddr,sid,serial#,event,p1raw,p2raw,blocking_session,last_call_et from v$session where sid in (48,50,31);

SADDR                   SID    SERIAL# EVENT                  P1RAW            P2RAW            BLOCKING_SESSION LAST_CALL_ET

---------------- ---------- ---------- ---------------------- ---------------- ---------------- ---------------- ------------

000000055E4BFA20        419      14671 PL/SQL lock timer      00               00                                        2475

000000055E99DF20        773      61023 library cache pin      00000005594D6AD8 00000005593BCC50              419           16

000000055E936160        807       1301 library cache lock     00000005594D6AD8 000000055A27BF50              773           14

查看具體鎖的信息

SQL> select kgllkhdl handle,kgllksnm sid,kgllkreq request, kglnaobj object,user_name

  2  from x$kgllk

  3  where kgllkses = ' 000000055E936160 '

  4  and kgllkreq > 0;

HANDLE                  SID    REQUEST OBJECT        USER_NAME

---------------- ---------- ---------- ------------- -------------

00000005594D6AD8           807          3 CALLING       SCOTT

可看到sid= 807 的會話正在請求CALLING上X模式的鎖

查看持有sid= 807 正在請求的鎖的會話信息：

SQL> select kgllkses saddr, kgllkhdl handle,kgllksnm sid,kgllkmod mod, kglnaobj object,user_name

  2  from x$kgllk lock_a

  3  where kgllkmod > 0

  4  and exists(

  5   select lock_b.kgllkhdl

  6   from x$kgllk lock_b

  7   where kgllkses = ' 000000055E936160 '/* blocked session*/

  8   and lock_a.kgllkhdl =lock_b.kgllkhdl

  9   and kgllkreq > 0

10  );

SADDR             HANDLE                  SID        MOD OBJECT    USER_NAME

----------------- ---------------- ---------- ---------- --------- ----------

000000055E4BFA20   00000005594D6AD8          419          1 CALLING   SCOTT

000000055E99DF20   00000005594D6AD8          773          3 CALLING   SCOTT

可看到sid=773的會話正在以X模式持有鎖，sid=419的會話正在以Null模式持有鎖，所以sid= 773 的會話阻塞了sid=807的會話

查看所有被阻塞的會話：

SQL> select sid, serial#,sql_id,username,terminal, program

  2  from v$session

  3  where saddr in(

  4   select kgllkses

  5   from x$kgllk lock_a

  6   where kgllkreq > 0

  7   and exists(

  8    select lock_b.kgllkhdl

  9    from x$kgllk lock_b

10    where kgllkses = ' 000000055E4BFA20 ' /* blocking session*/

11    and lock_a.kgllkhdl =lock_b.kgllkhdl

12    and kgllkreq = 0

13   )

14  );

       SID    SERIAL# SQL_ID        USERNAME                       TERMINAL                       PROGRAM

---------- ---------- ------------- ------------------------------ ------------------------------ ------------------------------------------------

        807        1301  77xa8gx8dmxh7 SCOTT                          pts/2                          sqlplus@bd-dev-rac1 (TNS V1-V3)

查看持有library cache pin的會話正在進行的操作：

SQL> select s.sid,s.serial#,p.kglpnmod,p.kglpnhdl,s.sql_id,p.kglpnmod lmode,o.spid "os process",obj.kglnaown pin_obj_owner,obj.kglnaobj pin_obj_name

  2  from x$kglpn p, v$session s, v$process o,x$kglob obj

  3  where p.kglpnuse =s.saddr

  4  and s.paddr = o.addr

  5  and p.kglpnmod > 0

  6  and p.kglpnhdl = obj.kglhdadr;

       SID    SERIAL#   KGLPNMOD KGLPNHDL         SQL_ID             LMODE os process               PIN_OBJ_OW PIN_OBJ_NAME

---------- ---------- ---------- ---------------- ------------- ---------- ------------------------ ---------- --------------------

       419      14671          2 000000055B26FF90 5whptv3nstk28          2 17898                    SYS        DBMS_LOCK

       419      14671          2 000000055B3486D8 5whptv3nstk28          2 17898                    SYS        DBMS_LOCK

       419      14671          2 00000005594D6AD8 5whptv3nstk28          2 17898                    SCOTT      CALLING

library cache pin信息：

SQL> desc x$kglpn;

名稱 類型

------------ ----------------------------

ADDR RAW(4)

INDX NUMBER

INST_ID NUMBER

KGLPNADR RAW(4)

KGLPNUSE RAW(4) ---會話地址(對應v$session的saddr)

KGLPNSES RAW(4) ---owner地址

KGLPNHDL RAW(4) ---句柄

KGLPNLCK RAW(4)

KGLPNCNT NUMBER

KGLPNMOD NUMBER ---持有pin的模式( 0為no lock/pin held，2為share，3為exclusive )

KGLPNREQ NUMBER ---請求pin的模式( 0為no lock/pin held， 2為share，3為exclusive )

KGLPNDMK NUMBER

KGLPNSPN NUMBER ---對應跟蹤文件的savepoint的值

library cache lock信息：

SQL> desc x$kgllk;

名稱 類型

---------- -----------

ADDR RAW(4)

INDX NUMBER

INST_ID NUMBER

KGLLKADR RAW(4)

KGLLKUSE RAW(4) ---會話地址(對應v$session的saddr)

KGLLKSES RAW(4) ---owner地址

KGLLKSNM NUMBER ---SID

KGLLKHDL RAW(4) ---library cache object 句柄

KGLLKPNC RAW(4) ---the address of the call pin

KGLLKPNS RAW(4) ---對應跟蹤文件中的session pin值

KGLLKCNT NUMBER

KGLLKMOD NUMBER ---持有鎖的模式( 0為no lock/pin held，1為null，2為share，3為exclusive )

KGLLKREQ NUMBER ---請求鎖的模式( 0為no lock/pin held，1為null，2為share，3為exclusive )

KGLLKFLG NUMBER ---cursor的狀態﹐8(10g前)或2048(10g)表示這個sql正在運行﹐

KGLLKSPN NUMBER ---對應跟蹤文件的savepoint的值

KGLLKHTB RAW(4)

KGLNAHSH NUMBER ---sql的hash值(對應v$session的sql_hash_value)

KGLLKSQLID VARCHAR2(13) ---sql ID，sql標識符

KGLHDPAR RAW(4) ---sql地址(對應v$session的sql_address)

KGLHDNSP NUMBER

USER_NAME VARCHAR2(30) ---會話的用戶名

KGLNAOBJ VARCHAR2(60) ---對象名稱或者已分析并打開cursor的sql的前60個字符

library cache object信息:

SQL> desc x$kglob

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

ADDR                                               RAW(8)

INDX                                               NUMBER

INST_ID                                            NUMBER

KGLHDADR                                           RAW(8) #游標地址

KGLHDPAR                                           RAW(8) # 父 游標地址

KGLHDCLT                                           NUMBER

KGLNAOWN                                           VARCHAR2(64) #對象屬主

KGLNAOBJ                                           VARCHAR2(1000) #對象名

KGLFNOBJ                                           CLOB

KGLNADLK                                           VARCHAR2(64)

KGLNAHSH                                           NUMBER

KGLNAHSV                                           VARCHAR2(32)

KGLNATIM                                           DATE

KGLNAPTM                                           DATE

KGLHDNSP                                           NUMBER

KGLHDNSD                                           VARCHAR2(64)

KGLHDLMD                                           NUMBERk

KGLHDPMD                                           NUMBER

KGLHDFLG                                           NUMBER

KGLHDOBJ                                           RAW(8)

KGLHDLDC                                           NUMBER

KGLHDIVC                                           NUMBER

KGLHDEXC                                           NUMBER

KGLOBHD0                                             RAW(8) #heap 0

KGLOBHD6                                             RAW(8) #heap 6

......