library cache lock

library cache lock、library cache pin原理分析：
http://www.itpub.net/thread-1826347-1-1.html

Oracle Shared pool 详解：
http://blog.csdn.net/tianlesoftware/article/details/6560956

Oracle Latch 说明：
http://blog.csdn.net/xujinyang/article/details/6832692

Oracle Mutex 机制 说明：
http://blog.csdn.net/tianlesoftware/article/details/6455517

Oracle Library cache 内部机制 说明：
http://blog.csdn.net/tianlesoftware/article/details/6629869

Oracle Library Cache Lock 解决思路
http://blog.csdn.net/tianlesoftware/article/details/7956996

SQL>select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event';
Enter value for event: library cache lock
old   1: select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event'
new   1: select name, parameter1, parameter2, parameter3 from v$event_name where name like 'library cache lock'

NAME                                PARAMETER1                PARAMETER2                PARAMETER3
----------------------------------- ------------------------- ------------------------- -------------------------
library cache lock                  handle address            lock address              100*mode+namespace

SQL>


wait: library cache lock

The library cache lock controls the concurrency between clients of the library cache by acquiring 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 (no other client can change the object).

This lock is also obtained as part of the operation to locate an object in the library cache (a library cache child latch is obtained to scan a list of handles, then the lock is placed on the handle once the object has been found).这个锁也是作为在库缓存中定位对象的操作的一部分而获得的（一个库高速缓存子锁存器被用来扫描一系列的手柄，然后一旦找到该对象就将锁放置在手柄上）。

解决：

1.Cause Identified: Unshared SQL Due to Literals
确定原因：由于常量原因导致的非共享SQL

Cause Justification：导致理由
TKProf:
    Use the report sorted by elapsed parse time
    Look at the top statements and determine if they are being hard parsed; these will have "Misses in the library cache" equal or close to the total number of parses
    Examine the statements that are being hard parsed and look for the presence of literal values.


1.1 Solution Identified: Rewrite the SQL to use bind values
解决方案标识：重写SQL以使用绑定值

1.2 Solution Identified: Use the CURSOR_SHARING initialization parameter
解决方案标识：使用CURSOR_SHARING初始化参数

2.Cause Identified: Shared SQL being aged out
原因确定：共享SQL正在老化。共享池太小，导致很多可以共享的语句超出库高速缓存，然后重新加载。 每次重新加载都需要硬解析并影响CPU和锁存器。

Cause Justification：
TKProf:
    Use the report sorted by elapsed parse time
    Look at the top statements and determine if they are being hard parsed; these will have "Misses in the library cache" equal or close to the total number of parses
    Examine the statements that are being hard parsed and look for the ABSENCE of literal values, this means these statements could have been shared but weren't (this is not entirely reliable since you could have statements that use binds but will not be executed again).

AWR or statspack reports:
    Library Cache statistics section shows that reloads are high (usually several thousand per hour) and little or no invalidations are seen
    The "% SQL with executions>1" is over 60%, meaning statements are being shared

2.1 Solution Identified: Increase the size of the shared pool
解决方案标识：增加共享池的大小

Increasing the shared pool size will reduce the need to age out statements that could be shared.
增加共享池大小将减少需要使可以共享的语句老化。

2.2 Solution Identified: 10g+: Use the Automatic Shared Memory Manager (ASMM) to adjust the shared pool size
解决方案标识：10g：使用自动共享内存管理器（ASMM）调整共享池大小

ASMM will automate memory sizing for the shared pool to ensure an optimal amount is available. You will need to set a reasonable value for SGA_MAX_SIZE and SGA_TARGET to enable ASMM.ASMM将自动调整共享池的内存大小，以确保可用的最佳金额。 您需要为SGA_MAX_SIZE和SGA_TARGET设置一个合理的值来启用ASMM。

2.3 Solution Identified: Keep ("pin") frequently used large PL/SQL and cursor objects in the shared pool

Use the DBMS_SHARED_POOL.KEEP() procedure to mark large, frequently used PL/SQL and SQL objects in the shared pool and avoid them being aged out. This will reduce reloads and fragmentation since the object doesn't need to keep re-entering the shared pool over and over.
使用DBMS_SHARED_POOL.KEEP（）过程在共享池中标记大型，经常使用的PL / SQL和SQL对象，并避免它们超时。 这将减少重新加载和碎片，因为对象不需要一直重复进入共享池。

3.Cause Identified: Library cache object Invalidations识别原因：库缓存对象失效
When objects (like tables or views) are altered via DDL or collecting statistics, the cursors that depend on them are invalidated. This will cause the cursor to be hard parsed when it is executed again and will impact CPU and latches当通过DDL或收集统计信息更改对象（如表或视图）时，依赖于它们的游标将失效。 这会导致游标再次执行时被硬解析，并会影响CPU和锁存器

Cause Justification导致理由

TKProf:
    Use the report sorted by elapsed parse time
    Look at the top statements and determine if they are being hard parsed; these will have "Misses in the library cache" equal or close to the total number of parses
    Examine the statements that are being hard parsed and look for the ABSENCE of literal values, this means these statements could have been shared but weren't (this is not entirely reliable since you could have statements that use binds but will not be executed again).

AWR or statspack reports:
    Library Cache statistics section shows that reloads are high (usually several thousand per hour) and invalidations are high
    The "% SQL with executions>1" is over 60%, meaning statements are being shared
    Check the Dictionary Statistics section of the report and look for non-zero values in the Modification Requests column, meaning that DDL occurred on some objects.

3.1 Solution Identified: Do not perform DDL operations during busy periods解决方案标识：在繁忙时段不要执行DDL操作

DDL will often cause library cache objects to be invalidated and this could cascade to many different dependent objects like cursors. Invalidations have a large impact on the library cache, shared pool, row cache, and CPU since they will likely require many hard parses to occur at the same time.

3.2 Solution Identified: Do not collect optimizer statistics during busy periods解决方案确定：在繁忙时段不要收集优化器统计信息

Collecting statistics (using ANALYZE or DBMS_STATS) will cause library cache objects to be invalidated and this could cascade to many different dependent objects like cursors. Invalidations have a large impact on the library cache, shared pool, row cache, and CPU since they will likely require many hard parses to occur at the same time.

 For some database versions, the DBMS_STATS procedure allows give you the option of not invalidating objects (see the "no_invalidate" option).
 对于某些数据库版本，DBMS_STATS过程允许您选择不使对象无效（请参阅"no_invalidate"选项）。

3.3 Solution Identified: Do not perform TRUNCATE operations during busy periods 解决方案标识：在繁忙时段不要执行TRUNCATE操作

4. Cause Identified: Objects being compiled across sessions识别原因：正在跨会话编译的对象
One or more sessions are compiling objects (typically PL/SQL) while another session wants to pin the same object prior to executing or compiling it. One or more sessions will wait on library cache pin in Share mode (if it just wants to execute it) or eXclusive mode (if it want to compile/change the object).
一个或多个会话正在编译对象（通常是PL / SQL），而另一个会话想要在执行或编译之前固定同一个对象。 一个或多个会话将在共享模式（如果它只是想执行它）或独占模式（如果它想编译/更改对象）在库缓存引脚上等待。

Cause Justification

TKProf:
    library cache pin waits and / or library cache pin waits
    Statement is compiling or executing PL/SQL
    
4.1 Solution Identified: Avoid compiling objects in different sessions at the same time or during busy times
解决方案标识：避免同时或在繁忙时间在不同会话中编译对象

Do not compile interdependent objects across concurrent sessions or during peak usage.
The HangAnalyze command can usually help identify the blockers, waiters, and the SQL which is causing the waits (see the "Hang / Locking tab > Issue Identification > Data Collection" for more information).
不要在并发会话或高峰使用期间编译相互依赖的对象。HangAnalyze命令通常可以帮助识别阻塞程序，等待程序和造成等待的SQL（有关详细信息，请参阅"挂起/锁定选项卡>问题识别>数据收集"）。

5. Cause Identified: Auditing is turned on确定原因：审核已打开

Auditing will increase the need to acquire library cache locks and potentially increase contention for them. This is especially true in a RAC environment where the library cache locks become database-wide (across all instances).

Cause Justification

AWR / Statspack:

    library cache lockwaits
    audit_trail parameter is set to something other than "none"

 
Solution Identified: Evaluate the need to audit 解决方案确定：评估需要审计

Consider disabling auditing if it is not absolutely necessary.如果不是绝对必要，考虑禁用审计。

6. Cause Identified: Unshared SQL in a RAC environment确定的原因：RAC环境中的非共享SQL

Library cache locks waits may occur in RAC environments when applications are not sharing SQL. In single-instance environments, library cache and shared pool latch contention is typically the symptom for unshared SQL. However, in RAC, the main symptom may be library cache lock contention.
当应用程序不共享SQL时，RAC环境中可能会出现库高速缓存锁定等待。 在单实例环境中，库缓存和共享池锁存争用通常是非共享SQL的症状。 但是，在RAC中，主要症状可能是库缓存锁争用。

Cause Justification
RAC environment

TKProf:
    Many statements are hard parsed
    library cache lock waits occur as part of a hard parse

AWR / Statspack:
    library cache lockwaits
    Low percentage for "% SQL with executions>1" (less than 60%)
    soft parse ratio is below 80%
 
6.1 Solution Identified: Rewrite the SQL to use bind values解决方案标识：重写SQL以使用绑定值
Rewriting the SQL to use bind values will allow the statement to be reused when specific values in the statement change but the overall statement is the same. This is the best way to promote sharing of SQL statements in the library cache.

6.2 Solution Identified: Use the CURSOR_SHARING initialization parameter


7. Cause Identified: Extensive use of row level triggers识别原因：广泛使用行级触发器
When row level triggers are fired frequently, higher than usual library cache activity may occur, because of the need to check if mutating tables are being read. During trigger execution, it is possible that the application tries to read mutating tables, i.e., tables that are in the process of being modified by the statement that caused the trigger to fire. As this may lead to inconsistencies, it is not allowed, and the application should receive the error ORA-4091. The mechanism to detect this error involves one library cache lock acquisition per table referenced in each select statement executed.
当行级触发器经常被触发时，由于需要检查是否正在读取突变表，所以可能会出现高于平常的库高速缓存活动。 在触发器执行期间，应用程序可能尝试读取变异表，即正在由触发器引发的语句修改的表。 由于这可能会导致不一致，所以不允许，应用程序应该收到错误ORA-4091。 检测此错误的机制涉及每个执行的select语句中引用的每个表的一个库高速缓存锁获取。

The extent of the problem depends on how many times the row triggers fire rather than on the number of row triggers have been created (i.e., one trigger that fires 10000 times will cause more problems than 100 triggers that fire once).
问题的严重程度取决于行触发的次数，而不是行触发器的创建次数（即一次触发10000次会导致比100次触发更多的问题）。

Cause Justification

TKProf:
    Many statements are hard parsed
    library cache lockwaits
    evidence of a row level trigger firing (maybe some recursive SQL related to a trigger)

Solution Identified: Evaluate the need for the row trigger解决方案确定：评估行触发器的需要

Sometimes row triggers aren't needed to accomplish the functionality. Consider if there is an alternative.
有时行触发器不需要完成功能。 考虑是否有其他选择。

8. Cause Identified: Excessive Amount of Child Cursors原因确定：过量的子游标
A large number of child cursors are being created for some SQL statements. This activity is causing contention among various sessions that are creating child cursors concurrently or with other sessions that also need similar resources (latches and mutexes).
一些SQL语句正在创建大量的子游标。 此活动正在同时创建子游标的各种会话之间或与其他需要类似资源（锁存器和互斥锁）的会话之间发生争用。

Cause Justification：导致理由
AWR / Statspack reports; look in the "SQL ordered by Version Count" section. If there are any SQL statements with more than 500 versions, then this problem is likely to be occurring. Alternatively, you can query V$SQLAREA to look for any SQL with version_count greater than 500.
AWR / Statspack报告; 查看"按版本计数排序的SQL"部分。 如果有超过500个版本的SQL语句，那么这个问题可能会发生。 或者，您可以查询V $ SQLAREA以查找version_count大于500的任何SQL。

Query V$SQL_SHARED_CURSOR to see the reasons why SQL isn't being shared
查询v$sql_shared_cursor去查找sql未共享的原因

8.1 Solution Identified: Inappropriate use of parameter CURSOR_SHARING set to SIMILAR
解决方案确定：不正确地使用参数CURSOR_SHARING设置为SIMILAR

The difference between SIMILAR and FORCE is that SIMILAR forces similar statements to share the SQL area without deteriorating execution plans. Setting CURSOR_SHARING to FORCE forces similar statements to share the SQL area potentially deteriorating execution plans.

=================================================
实验library cache lock等待事件：
create or replace procedure rhys(amy_sleep    in boolean,
                                 rhys_compile in boolean) as
begin
  if (amy_sleep) then
    dbms_lock.sleep(3000);
  elsif (rhys_compile) then
    execute immediate 'alter procedure rhys compile';
  end if;
end;
/


session 1：
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        36

--在session 1创建上述procedure并执行：
SQL> create or replace procedure rhys(amy_sleep    in boolean,
  2                                   rhys_compile in boolean) as
  3  begin
  4    if (amy_sleep) then
  5      dbms_lock.sleep(3000);
  6    elsif (rhys_compile) then
  7      execute immediate 'alter procedure rhys compile';
  8    end if;
  9  end;
 10  /

Procedure created.

SQL>
SQL> execute rhys(true,false);

hang.................


session 2：

SQL> select sid from v$mystat where rownum=1;

       SID
----------
        70

SQL>    
SQL> execute rhys(false ,true);

hang.................


session 3:
SQL>  select sid,username,sql_id,p1,p1raw,p2,p2raw,p3,p3raw,wait_time,seconds_in_wait,blocking_session from v$session where event='library cache pin';

       SID USERNAME   SQL_ID                P1 P1RAW                    P2 P2RAW                    P3 P3RAW             WAIT_TIME SECONDS_IN_WAIT BLOCKING_SESSION
---------- ---------- ------------- ---------- ---------------- ---------- ---------------- ---------- ---------------- ---------- --------------- ----------------
        70 SYS        2yv7ja732z3p0 1914185944 00000000721828D8 1950900864 0000000074486280 3.8889E+14 000161B100010003          0             196               70

SQL>  select sid,username,sql_id,p1,p1raw,p2,p2raw,p3,p3raw,wait_time,seconds_in_wait,blocking_session from v$session where event='library cache pin';

       SID USERNAME   SQL_ID                P1 P1RAW                    P2 P2RAW                    P3 P3RAW             WAIT_TIME SECONDS_IN_WAIT BLOCKING_SESSION
---------- ---------- ------------- ---------- ---------------- ---------- ---------------- ---------- ---------------- ---------- --------------- ----------------
        70 SYS        2yv7ja732z3p0 1914185944 00000000721828D8 1950900864 0000000074486280 3.8889E+14 000161B100010003          0             209               70

SQL>
SQL>  select sid,username,sql_id,p1,p1raw,p2,p2raw,p3,p3raw,wait_time,seconds_in_wait,blocking_session from v$session where event='library cache pin';

       SID USERNAME   SQL_ID                P1 P1RAW                    P2 P2RAW                    P3 P3RAW             WAIT_TIME SECONDS_IN_WAIT BLOCKING_SESSION
---------- ---------- ------------- ---------- ---------------- ---------- ---------------- ---------- ---------------- ---------- --------------- ----------------
        70 SYS        2yv7ja732z3p0 1914185944 00000000721828D8 1950900864 0000000074486280 3.8889E+14 000161B100010003          0             211               70

可以看到等待一直在增加。

--通过sql_id可以得到sql
SQL> select sql_text from v$sql where sql_id='2yv7ja732z3p0';

SQL_TEXT
-------------------------------------------------
alter procedure rhys compile

SQL>


处理Library cache lock
 
使用hanganalyze  + systemstat 分析
Systemstat 事件包含每个oracle 进程的详细信息。当操作hang住时，可以新开一个窗口，使用该事件，捕获相关信息。

Systemdump 级别说明：
LEVEL参数：
10   Dump all processes (IGN state)

5    Level 4 + Dump all processes involved in wait chains (NLEAF state)

4    Level 3 + Dump leaf nodes (blockers) in wait chains(LEAF,LEAF_NW,IGN_DMP state)

3    Level 2 + Dump only processes thought to be in a hang (IN_HANG state)

1-2  Only HANGANALYZE output, no process dump at all

level 266= SYSTEM STATE (level=10, withshort stacks) =  level 10 + short stacks

level 266 在level 10的基础上包含了进程的short stacks信息

Oracle 9.2.0.1 之后，执行如下脚本：
$sqlplus '/ as sysdba'
oradebugs etmypid
oradebug unlimit
oradebug dump systemstate 266
oradebug tracefile_name

systemstat 226级别在9.2.0.6 之前不可用，所以在之前的版本可以使用如下命令：
alter session set max_dump_file_size=unlimited;
alter session set events 'immediate trace name systemstate level 10'

方法一）：sql查询
 select distinct ses.ksusenum      sid,
                 ses.ksuseser      serial#,
                 ses.ksuudlna      username,
                 ses.ksuseunm      machine,
                 ob.kglnaown       obj_owner,
                 ob.kglnaobj       obj_name,
                 pn.kglpncnt       pin_cnt,
                 pn.kglpnmod       pin_mode,
                 pn.kglpnreq       pin_req,
                w.state,
                w.event,
                w.wait_Time,
                w.seconds_in_Wait
  from x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w
 where pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)
   and ob.kglhdadr = pn.kglpnhdl
   and pn.kglpnuse = ses.addr
   and w.sid = ses.indx
 order by seconds_in_wait desc；

执行：
SQL>  select distinct ses.ksusenum      sid,
  2                   ses.ksuseser      serial#,
  3                   ses.ksuudlna      username,
  4                   ses.ksuseunm      machine,
  5                   ob.kglnaown       obj_owner,
  6                   ob.kglnaobj       obj_name,
  7                   pn.kglpncnt       pin_cnt,
  8                   pn.kglpnmod       pin_mode,
  9                   pn.kglpnreq       pin_req,
 10                  w.state,
 11                  w.event,
 12                  w.wait_Time,
 13                  w.seconds_in_Wait
 14    from x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w
 15   where pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)
 16     and ob.kglhdadr = pn.kglpnhdl
 17     and pn.kglpnuse = ses.addr
 18     and w.sid = ses.indx
 19   order by seconds_in_wait desc
 20  ;
  SID    SERIAL# USERNAME   MACHINE         OBJ_OWNER  OBJ_NAME           PIN_CNT   PIN_MODE    PIN_REQ STATE      EVENT                 WAIT_TIME SECONDS_IN_WAIT----- ---------- ---------- --------------- ---------- --------------- ---------- ---------- ---------- ---------- -------------------- ---------- ---------------
   36        663 SYS        oracle          SYS        RHYS                     3          2          0 WAITING    PL/SQL lock timer             0            1778
   70        475 SYS        oracle          SYS        RHYS                     0          0          3 WAITING    library cache pin             0             785
   70        475 SYS        oracle          SYS        RHYS                     3          2          0 WAITING    library cache pin             0             785

可以看到sid 36的会话执行时间最长且未同一对象，kill即可。

方法二） dump systemstate 266

SQL>  oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump  systemstate 266
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_13643.trc
SQL>

搜索关键字：There is at least one session blocking this session. Dumping 2 direct blocker(s):
 
如下:
    (session) sid: 70 ser: 475 trans: 0x8ead54b0, creator: 0x917aaeb0
              flags: (0x100041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
              flags2: (0x48009) -/DDLT2/INC
              DID: , short-term DID:
              txn branch: (nil)
              edition#: 100              oct: 25, prv: 0, sql: 0x7205ca40, psql: 0x7465e618, user: 0/SYS
    ksuxds FALSE at location: 0
    service name: SYS$USERS
    client details:
      O/S info: user: oracle, term: pts/1, ospid: 13593
      machine: wang program: sqlplus@wang (TNS V1-V3)
      application name: sqlplus@wang (TNS V1-V3), hash value=1012509018
    Current Wait Stack:
     0: waiting for 'library cache pin'
        handle address=0x721828d8, pin address=0x74486280, 100*mode+namespace=0x161b100010003
        wait_id=13 seq_num=14 snap_id=1
        wait times: snap=5 min 55 sec, exc=5 min 55 sec, total=5 min 55 sec
        wait times: max=15 min 0 sec, heur=5 min 55 sec
        wait counts: calls=120 os=120
        in_wait=1 iflags=0x15a2
    There is at least one session blocking this session.
      Dumping 2 direct blocker(s):
        inst: 1, sid: 70, ser: 475
        inst: 1, sid: 36, ser: 663
      Dumping final blocker:
        inst: 1, sid: 70, ser: 475
    There are 0 sessions blocked by this session.
    Dumping one waiter:
      inst: 1, sid: 70, ser: 475
      wait event: 'library cache pin'
        p1: 'handle address'=0x721828d8
        p2: 'pin address'=0x74486280
        p3: '100*mode+namespace'=0x161b100010003
      row_wait_obj#: 4294967295, block#: 0, row#: 0, file# 0
      min_blocked_time: 354 secs, waiter_cache_ver: 36848
    Wait State:
      fixed_waits=0 flags=0x22 boundary=(nil)/-1
    Session Wait History:
        elapsed time of 0.007600 sec since current wait
     0: waited for 'SQL*Net message from client'
        driver id=0x62657100, #bytes=0x1, =0x0
        wait_id=12 seq_num=13 snap_id=1
        wait times: snap=4 min 5 sec, exc=4 min 5 sec, total=4 min 5 sec
        wait times: max=infinite
        
方法三）oradebug hanganalyze 3

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug hanganalyze 3
Cycle 1: (1/70)
Hang Analysis in /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_14375.trc
SQL> oradebug close_trace
Statement processed.
SQL>
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_14375.trc
SQL>

*** 2017-12-13 09:46:25.762
===============================================================================
END OF HANG ANALYSIS
===============================================================================

*** 2017-12-13 09:46:25.762
===============================================================================
HANG ANALYSIS DUMPS:
  oradebug_node_dump_level: 3
===============================================================================
 
State of LOCAL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[35]/1/36/663/0x90e7e820/13435/LEAF/
[69]/1/70/475/0x90e16a60/13594/IN_HANG/[69][35]
 
可以看到70（sid) 阻塞了36（sid)
 
 
方法四）查看X$KGLLK表  （为验证，根据mos:How to Find which Session is Holding a Particular Library Cache Lock (文档 ID 122793.1))
The X$KGLLK table (accessibleonly as SYS/INTERNAL) contains all the library object locks (both held &requested) for all sessions and is more complete than the V$LOCK  view although the column names don't always reveal their meaning.
--X$KGLLK 表只能被SYS/INTERNAL用户访问，其包含所有library object locks的信息（held和requested）。
 
--查看等待事件为librarycache lock的session 的session address (SADDR):

SQL>select sid,saddr from v$session where event='library cache lock';
SID SADDR
---------- --------
16 572ed244

--从x$kgllk查看具体的锁信息：
select kgllkhdl Handle, kgllkreq Request,kglnaobj Object
  from x$kgllk
 where kgllkses = '572ed244'
   and kgllkreq > 0;

HANDLE      REQUEST   OBJECT
-------- ---------- -------------------
62d064dc          2EMPLOYEES

KGLLKREQ: This will show you the library cache lock requested by this session(KGLLKREQ > 0)
KGLNAOBJ:contains the first 80 characters of the name of the object.
KGLLKHDL:corresponds with the 'handle address' of the object


--然后根据KGLLKHDL从X$KGLLK查看KGLLKMOD > 0的session，其正在持有该锁：
select kgllkses saddr, kgllkhdl handle,kgllkmod mod, kglnaobj object
   from x$kgllk lock_a
 where kgllkmod > 0
   andexists (select lock_b.kgllkhdl
          from x$kgllk lock_b
         where kgllkses = '572ed244'/* blocked session*/
           and lock_a.kgllkhdl =lock_b.kgllkhdl
           and kgllkreq > 0);

SADDR    HANDLE         MOD    OBJECT
-------- ----------- ------- --------
572eac94  62d064dc      3      EMPLOYEES

--查看所有blocked的session：
selectsid, username,terminal, program
  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 = '572eac94'/* blocking session*/
                   and lock_a.kgllkhdl =lock_b.kgllkhdl
                   and kgllkreq = 0));


--查看所有持有librarycache pin 或者lock的session 在做什么：
SELECT s.sid, kglpnmod"Mode",kglpnreq "Req", SPID "OS Process"
  FROM v$session_wait w,x$kglpn p, v$session s, v$process o
 WHERE p.kglpnuse =s.saddr
   AND kglpnhdl = w.p1raw
   and w.event like'%library cache %'
   and s.paddr = o.addr

 
最终处置
一般来说，根据上述方法都可以找到library cache lock的根源，确定是哪个session 导致的，如我们上面的hanganalyze中，是36的session。 我们只需要kill 掉这个session，其他的问题就会自动解决了。

先在DB级别kill session，如果kill 不了，在os 级别kill。

alter system kill session '36,663' immediate;

注意在os 级别kill 之前，先用ps 命令查看一下该进程，如果是DB 进程，不可随意kill，否则会导致系统crash。

ps -ef|grep 13435
kill -9 13435

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2149682/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2149682/