oracle出现队列等待的原因,案例:Oracle等待事件library cache lock故障优化处理总结 分析产生原因...

天萃荷净

业务系统很缓慢，查看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故障优化处理总结 分析产生原因