脚本查找"library cache lock"阻塞的会话

最新推荐文章于 2024-03-18 17:59:28 发布

原创最新推荐文章于 2024-03-18 17:59:28 发布 · 283 阅读

1 ·

CC 4.0 BY-SA版权

脚本专栏收录该内容

8 篇文章

订阅专栏

本文详细介绍了如何使用Oracle数据库的内部视图和动态性能视图来诊断和解决由library cache lock引起的会话阻塞问题。通过SQL查询，可以定位到阻塞的会话、被阻塞的会话以及涉及的对象，从而快速识别并解决数据库性能瓶颈。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

以sys用户登录

—找出library cache lock对应的sid

select sid,saddr from v$session where event= 'library cache lock';

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

—根据saddr查找请求（被阻塞）的会话以及对象

select kgllkhdl Handle,kgllkreq Request, kglnaobj Object
from x$kgllk where kgllkses = '572ed244'
and kgllkreq > 0;

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

—根据saddr查找阻塞的会话以及对象

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-blocking sid  62d064dc   3    EMPLOYEES

—查找阻塞会话的sid以及客户端信息

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)

—根据阻塞会话的sid查找所有被阻塞的会话

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)
);