sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA > Oracle等待事件 >

案例:Oracle等待事件library cache lock故障优化处理总结 分析产生原因

时间:2016-06-03 18:40   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净

业务系统很缓慢,查看Oracle数据库的library cache lock等待事件较多

1.Library cache lock介绍

Oracle利用Library cache lock和Library cache pin来实现并发控制,Library cache lock是在handle上获取的,而Library cache pin则是在data heap上获取。访问对象时,首先必须获取handle上的lock,然后将访问的数据pin在内存中。lock的作用是控制进程间的并发访问,而pin的作用是保证数据一致性,防止数据在访问时被交换出去。
lock和pin的实现类似于enqueue,在每个handle上都有lock和pin的holder list和waiter list,用来保存持有该资源和等待该资源的队列。

2.Library cache lock相关sql语句

--找出library cache lock等待sid,saddr信息
select sid,saddr from v$session where event= 'library cache lock';

SID        SADDR
---------- --------
16         572ed244

--找出blocked信息
select kgllkhdl Handle,kgllkreq Request, kglnaobj Object
from x$kgllk where kgllkses = '572ed244'
and kgllkreq > 0;

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

--找出blocking信息
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 = '572ed244' /* blocked session */
and lock_a.kgllkhdl = lock_b.kgllkhdl
and kgllkreq > 0);

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

--blocking 会话信息
select sid,username,terminal,program from v$session where saddr = '572eac94'

SID        USERNAME     TERMINAL  PROGRAM
---------- -----------  --------- --------------------------------------------
12          SCOTT        pts/20    sqlplus@goblin.forgotten.realms (TNS V1-V3)

--所有blocked 会话
select sid,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)
);

SID        USERNAME  TERMINAL  PROGRAM
---------- --------- --------- -------------------------------------------
13         SCOTT     pts/22    sqlplus@goblin.forgotten.realms (TNS V1-V3)
16         SCOTT     pts/7     sqlplus@goblin.forgotten.realms (TNS V1-V3)

3.案例分析library cache lock解决过程

使用命令
select SID from v$session_wait where event='library cache lock';
发现问题的sid为72、90、97
选择了72号进程来查找问题,新开一个session
SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';

Session altered.

SQL> SELECT PID FROM V$PROCESS WHERE ADDR=
  2          (SELECT PADDR FROM V$SESSION WHERE SID=72);
       PID
----------
       122

生成一个trace文件zhyz1_ora_4890752.trc
查看此文件,找122进程
PROCESS 122:
  ----------------------------------------
  SO: 7000001b58a9508, type: 2, owner: 0, flag: INIT/-/-/0x00
  (process) Oracle pid=122, calls cur/top: 7000001b9005c20/7000001b9005c20, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 0 0 0
              last post received-location: No post
              last process to post me: none
              last post sent: 0 0 0
              last post sent-location: No post
              last process posted by me: none
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 7000001b593eb68
    O/S info: user: oracle, term: UNKNOWN, ospid: 2269188
    OSD pid info: Unix process pid: 2269188, image: oracle@ltrz_db1 (TNS V1-V3)
    ----------------------------------------
    SO: 7000001b5974780, type: 4, owner: 7000001b58a9508, flag: INIT/-/-/0x00
    (session) trans: 0, creator: 7000001b58a9508, flag: (41) USR/- BSY/-/-/-/-/-
              DID: 0000-0000-00000000, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 3, prv: 0, sql: 7000001d04eca50, psql: 7000001d3c70968, user: 101/ACCTMP
    O/S info: user: Administrator, term: JF-T-WANGXM, ospid: 3504:3508, machine: MSHOME\JF-T-WANGXM
              program: DBArt701.exe
    application name: DBArt701.exe, hash value=0
    waiting for 'library cache lock' blocking sess=0x0 seq=1583 wait_time=0
                handle address=7000001d3378800, lock address=7000001c539a550, 100*mode+namespace=515
    temporary object counter: 0
……………………

handle address与v$session_wait中这个会话的P1RAW是一样的
根据这个地址在trace文件中继续查找到下面的信息
SO: 7000001c52306a0, type: 52, owner: 7000001c31c56d0, flag: INIT/-/-/0x00
        LIBRARY OBJECT PIN: pin=7000001c52306a0 handle=7000001d3378800 mode=X lock=0
        user=7000001b59864b0 session=7000001b59864b0 count=1 mask=0709 savepoint=15720 flags=[00]
这个信息属于103号进程(pid)
PROCESS 103:
  ----------------------------------------
  SO: 7000001b58a3608, type: 2, owner: 0, flag: INIT/-/-/0x00
  (process) Oracle pid=103, calls cur/top: 7000001cf47af78/7000001cf47af78, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
反查sid为99,此进程正在进行一些扩展动作,该进程运行完毕后,library cache lock也随之结束了。

另外,还采用了一种方法,能更快的找到问题session。
SQL>   SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
  2   WHERE SADDR in
  3    (SELECT KGLLKSES FROM X$KGLLK LOCK_A
  4     WHERE KGLLKREQ = 0
  5       AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
  6                   WHERE KGLLKSES = '07000001B5974780' /* BLOCKED SESSION */
  7                   AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
  8                   AND KGLLKREQ > 0)
  9    );
       SID USERNAME                       TERMINAL
---------- ------------------------------ ------------------------------
PROGRAM
------------------------------------------------
        99 ACCTMP                         JF-T-WANGXM
DBArt701.exe

SQL> SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
  2   WHERE SADDR in
  3    (SELECT KGLLKSES FROM X$KGLLK LOCK_A
  4     WHERE KGLLKREQ > 0
  5       AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
  6                   WHERE KGLLKSES = '07000001B59864B0' /* BLOCKING SESSION */
  7                   AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
  8                   AND KGLLKREQ = 0)
  9    );
       SID USERNAME                       TERMINAL
---------- ------------------------------ ------------------------------
PROGRAM
------------------------------------------------
        72 ACCTMP                         JF-T-WANGXM
DBArt701.exe
        90 ACCTMP                         JF-T-WANGXM
DBArt701.exe
        97 ACCTMP                         JF-T-WANGXM
DBArt701.exe

    第一个语句是根据被锁住的session来找到谁锁的,第二个语句是在一直持锁session的情况下找到都有谁在等待自己的锁,红字部分为v$session中的saddr。
  
    在手头有脚本的时候第二种方法可以很快的定位问题,第一种方法虽然有些繁琐,但能定位到更多的问题。


 


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:Oracle等待事件library cache lock故障优化处理总结 分析产生原因

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/275.html

Oracle研究中心

关键词:

Oracle等待事件

library cache lock故障优化处理总结

library cache lock分析产生原因