Librarycache结构与Librarycachelock、Librarycachepin等待事件

一、Library cache 结构

创新互联公司网站设计，为客户量身定制各类网站建设业务，包括企业型、电子商务型、自适应网站建设、行业门户型等各类网站，实战经验丰富，成功案例众多。以客户利益为出发点，创新互联公司网站制作为客户规划、定制设计符合企业需求、带有营销价值的网络建站方案认真对待每一个客户，我们不用口头的语言来吹擂我们的优秀，近1000家的成功案例见证着我们的成长。

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