Loading
0

Library cache pin & Library cache lock事件的诊断处理

这几天处理了一个Library cache pin事件,开发人员抱怨怎么编译一个存储过程半天没反应,正常应该瞬间就完成的,我第一反应应该是有锁没有释放导致编译存储过程Hang在那里,于是查看v$session视图发现有library cache pin的等待事件,找到阻塞的会话将其kill掉释放资源解决问题,下面我们来详细的了解一下Library cache pin & Library cache lock这两个等待事件和处理方法。

 

首先我们通常所说的library cache locklibrary cache pinenqueue,而不是latch,这是两种不同的DDL lock,在11gR1之前Oracle中存也存在名为library cache locklibrary cache pin的latch,他们属于library cache latches(包括library cache latch、library cache lock latch、library cache pin latch、library cache pin allocation latch、library cache load lock latch),The library cache latches serialize access to the objects in the library cache. Access to library cache objects always occurs through library cache locks. Because locking an object is not an atomic instruction, a library cache latch is acquired before the library cache lock request and is released after it. 换句话说,library cache lock latch是用来控制对library cache lock的并发访问的,library cache pin latch是用来控制对library cache pin的并发访问的。从10.2.0.2.0开始,Oracle将_kks_use_mutex_pin的默认值改成了true,这意味着从10.2.0.2开始,Oracle里将再不会有针对cursor的library cache pin等待,取而代之的是mutex等待,具体表现为cursor: pin *等待,如cursor: pin S wait on X,从11gR1开始Oracle用mutex完全替代了library cache latches。下面我们了解一下作为enqueue的library cache lock和library cache pin。

 

什么是library cache lock?

library cache lock是用来控制对library cache的并发使用的,它获取一个对象句柄(object handle)上的锁,从而:

  • 一个使用者可以防止其它使用者访问同一个对象。
  • 这个使用者可以长时间地维护一个依赖对象(例如,其它使用者不能更改这个对象)。

定位 library cache 中的一个对象同样也需要这个锁。

在解析或编译 SQL 或 PL/SQL 语句期间,我们需要获得被引用的数据库对象(表,视图,过程,函数,包,包体,触发器,索引,聚簇,同义词)的 library cache lock;这个锁在解析与编译结束时会被释放。

cursor(SQL 与 PL/SQL 区),管道(pipes)和其它的瞬时(transient)对象不使用这个锁。

Library cache lock 上的死锁不会被自动检测到,对其的操作是同步进行的。

Library cache lock有三种lock mode,分别是share、exclusive和null。

      • share - 如果一个进程打算只是读取一个对象的时候它会获取一个share lock。
      • exclusive - 如果一个进程打算创建或修改一个对象,比如它想drop掉一个对象,那么它会获取一个exclusive lock。
      • null - 这是一个特殊的情况,null lock是在被执行的对象上获取,比如child cursor, procedure, function, package, type body,它可以在一个对象上维持很长一段时间,如果对象变成无效,它可以监测到。它被用来作为通知会话一个可执行的对象不再可用的机制,你可以随时打破null lock,如果null lock被打破,那么这个对象就是无效的,它将会通知这个null lock的持有者这个对象需要被重新编译,这个null lock会在sql语句执行阶段的解析过程中被获取,并且只要这个语句还保留在shared pool中的shared SQL area,那么这个null lock就会被一只持有。

什么是"Library cache pin" ?

这个事件管理 library cache 并发。Pin 住一个对象会使它使用的 heap 被载入到内存中。如果一个使用者想要修改或检查这个对象,它必须在获得 lock 之后再取得一个 pin。Pin 可以用 NULL, SHARE, EXCLUSIVE 模式获得,并且可以看做是一种特殊的 lock。等待"library cache pin"意味着这个 PIN 正被某个其它 session 以不兼容的模式持有。

访问当前被缓存到 library cache 中的数据库对象(表,视图,过程,函数,包,包体,触发器,索引,聚簇,同义词)的时候需要获得 library cache pin; 在 library cache 中,数据库对象被缓存成两部分:句柄(handle)和对象(object); 这个锁(pin)是用来保护"object"部分的。

Library cache pin 上的死锁不会被自动检测到,对其的操作是同步进行的。

Library cache pin有两种lock mode,分别是share和exclusive。

      • share - 如果只有读的访问请求,那么这个对象只会被pin在share mode下,这是因为当一个对象从exclusive mode下unpinned的时候会出现null locks broken,所有依赖的瞬时对象(cursor)将会变成无效,会造成不必要的重编译和重解析。
      • exclusive - 如果一个对象需要被修改,那它必须被pin在exclusive mode下,但是process总是先将一个对象pin在share mode下,对它进行安全检查和错误检测,如果有必要(比如修改),那么进程会将其pin在exclusive mode下。

 

为什么需要这两种不同类型的锁?

Lock 与 pin 都用于访问在 library cache 中的对象。Lock 管理不同进程间的并发,pin 则管理缓冲区的一致性。为了访问一个对象,进程必须首先锁定(lock)这个对象的句柄(handle),然后它自己 pin 住对象的内存堆。

Lock 与 pin 请求会一直等待直到获得为止,这是一个引起争用的可能的原因,因为它没有 NOWAIT 请求模式。

通过获得一个在对象句柄上的锁,一个进程能防止其它进程访问这个对象,甚至不可以查看它的类型。它还能在维护对象依赖关系的同时阻止其它进程访问这个对象。获取一个 lock 同样也是在缓存中查找对象的唯一途径。查找并锁住对象是在一个操作中完成的。

如果一个进程想检查或修改一个对象,那么它必须获得一个在这个对象上的 pin (获得了在句柄上的锁之后)。Pin这个动作会将相应对象的信息载入到内存,如果之前没有的话,同时还能确保这些信息保留在内存直到 pin 被释放。

Oracle 在分析/编译 Package/Procedure/Function/View 时需要 Library Cache Lock 和 Library Cache Pin。这是为了确保在分析/编译期间, 没有其它人可以对这些对象的定义进行改变,或者删除、重建这个对象。

当一个 SQL 语句被一个 session 硬解析时,这个 session 需要获得一个 library cache lock 以便阻止其它 session 去访问或修改同一个对象。如果这个事件等待很长时间。这表明可能 shared pool 过小或经常发生对象被 flush 出去的清醒。还有,这表明数据库对象被经常修改。

除了硬解析,如果一个 session 要更改被 SQL 语句引用的对象的定义或对其做任何更改,就必须获得一个 library cache lock 和 library cache pin。需要 Pin 的原因是需要加载数据字典信息到内存中来修改这个对象。

 

通过v$session或v$session_wait里的P1、P2、P3这3个参数来定位wait的对象和blocker:

  • P1 = Handle address
  • P2 = Pin address
  • P3 = Encoded Mode & Namespace

 

  • Handle address

这个地址是等待中会话想要去获取一个pin的那个library cache对象的句柄地址。

我们可以用以下语句查找到那个句柄地址对应的对象,这里要使用的是P1RAW而不是P1的值。

  • Pin address

这个是PIN自身的地址,同样的我们要用的是P2RAW的值,而不是P2

 

  • Encoded Mode & Namespace

这个参数代表了锁模式代码和名称空间

In Oracle 7.0 - 8.1.7 inclusive the value is 10 * Mode + Namespace.

In Oracle 9.0 - 11.1 inclusive the value is 100 * Mode + Namespace.

Mode-锁的模式分别用以下数字代表

        • 2 - Share mode
        • 3 - Exclusive mode

Namespace is just the namespace number of the namespace in the library cache in which the required object lives:

        • 0 SQL Area
        • 1 Table / Procedure / Function / Package Header
        • 2 Package Body
        • 3 Trigger
        • 4 Index
        • 5 Cluster
        • 6 Object
        • 7 Pipe
        • 13 Java Source
        • 14 Java Resource
        • 32 Java Data

比如说P3RAW为c9,这是一个十六进制的数值,将其转换为10进制为201,通过100*mode+namespace公式得知mode值为2,namespace的值为1,可得知其为share mode锁,并且对象为Table / Procedure / Function / Package Header这一类

 

  • Wait Time

一个waiter会等一个pin变成可用3秒(PMON进程是等1秒),如果不可用那么这个会话会再一次等待,在视图v$session_wait中的SEQ#值会增加。

 

  • 找出阻塞者

下面的SQL通过给出等待的P1的值来找出持有对象PIN和请求对象PIN的会话:

给出更直接的SQL

找出lock的持有者和请求者

 

An X request (3) will be blocked by any pins held S mode (2) on the object.

An S request (2) will be blocked by any X mode (3) pin held, or may queue behind some other X request.

简单的说就是一个X(3)请求会被任何一个持有对象S(2)模式的pin所阻塞,一个S(2)请求会被任何一个持有对象X(3)模式的pin所阻塞,或者也许会队列到一些其他的X请求之后,lock也是一样。

所以根据这个原则我们很容易的通过上面的SQL找出阻塞者,通常情况下要去诊断一个阻塞场景为什么会出现需要Oracle的支持,如果你只是想立即解决这个问题,那么你可以根据上面SID和OS Process的信息去kill掉相应的会话。正确的诊断通常会要求你每隔30秒收集一次SYSTEMSTATE或者HANGANLYZE的dump,共收集3次,然后将完整的会话和相关对象的详细信息一起提交给Oracle支持,采集SYSTEMSTATE或者HANGANLYZE的dump需要拥有“ALTER SYSTEM”权限的用户去连接到instance,并发布以下命令:

从9.2.0.1版本开始我们也可以使用oradebug,执行如下命令来收集SYSTEMSTATE:

在version 9.2.0.6之前level 266是不可用的,需要用level 10代替。

 

这些操作会在DIAGNOSTIC_DEST(11g版本,早期版本在USER_DUMP_DEST目录下,如果是share server则在BACKGROUND_DUMP_DEST目录下)目录下产生trace文件,如果是Parallel Server或者RAC环境中,需要在每个节点都执行3遍。

 

Systemstate dump包含了每个process的相关信息并将它们分割开来,打开trace文件通过搜索"waiting for"来查找wait event,信息如下:

PROCESS 20:
----------------------------------------
SO: 0x7d2bd820, type: 2, owner: (nil), flag: INIT/-/-/0x00
(process) Oracle pid=20, calls cur/top: 0x7d3d62d0/0x7d3d85dc, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 109 0 4
last post received-location: kslpsr
last process to post me: 7d2b8d94 1 6
last post sent: 0 0 24
last post sent-location: ksasnd
last process posted by me: 7d2b8d94 1 6
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 0x7d2ed5dc
O/S info: user: oracle, term: pts/7, ospid: 19759
OSD pid info: Unix process pid: 19759, image: goblin.forgotten.realms (TNS V1-V3)
<cut>
(session) sid: 141 trans: (nil), creator: 0x7d2bd820, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0001-0014-00000668, short-term DID: 0000-0000-00000000
txn branch: (nil)
oct: 6, prv: 0, sql: 0x62d01c34, psql: 0x7c20f24c, user: 542/SCOTT
service name: SYS$USERS
O/S info: user: oracle, term: pts/7, ospid: 19758, machine: goblin.forgotten.realms
program: sqlplus@goblin.forgotten.realms (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
waiting for 'library cache lock' blocking sess=0x(nil) seq=36 wait_time=0 seconds since wait started=11
handle address=62d064dc, lock address=79f88a68, 100*mode+namespace=c9

 

  • 通过handle address找到对象上锁的信息:

SO: 0x79f88a68, type: 53, owner: 0x7d3d62d0, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=79f88a68 handle=62d064dc request=S
call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
htl=0x79f88ab4[0x79e71e60,0x79e71e60] htb=0x79e71e60 ssga=0x79e716fc
user=7d3a13b8 session=7d3a13b8 count=0 flags=[0000] savepoint=0xce
LIBRARY OBJECT HANDLE: handle=62d064dc mtx=0x62d06590(0) cdp=0
name=SCOTT.EMPLOYEES

 

我们可以看到对象所是以share mode(request=S)被请求的,对象的名称是SCOTT.EMPLOYEES

 

  • 使用“handle address”在同一个trace文件中找到在你的资源上持有锁的进程

PROCESS 18:
----------------------------------------
SO: 0x7d2bcca8, type: 2, owner: (nil), flag: INIT/-/-/0x00
(process) Oracle pid=18, calls cur/top: 0x79f3ab84/0x7d3d5fc8, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 109 0 4
last post received-location: kslpsr
last process to post me: 7d2b8d94 1 6
last post sent: 0 0 24
last post sent-location: ksasnd
last process posted by me: 7d2b8d94 1 6
<cut>
SO: 0x75fe8f7c, type: 53, owner: 0x7b751914, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=75fe8f7c handle=62d064dc mode=X
call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
htl=0x75fe8fc8[0x79f81790,0x79fc3ef8] htb=0x79f81790 ssga=0x79f8102c
user=7d3988d0 session=7d3988d0 count=1 flags=[0000] savepoint=0x146e
LIBRARY OBJECT HANDLE: handle=62d064dc mtx=0x62d06590(0) cdp=0
name=SCOTT.EMPLOYEES

 

从输出上我们可以看到Process 18(pid)正持有着我们要访问的对象上的一个exclusive lock (mode=X),通过使用v$process和v$session我们可以得到这个进程的一些其他信息,比如sid,user,program等等。

 

  • 全系统等待-Systemwide Waits

如果"library cache pin" waits形成了大量的等待时间,那么确定是只有一两个会话等待了很长时间还是大量的进程间形成了一个更严重的争用是很重要的。

    • 查看v$session_wait来确定wait是牵扯了大量的会话还是只是孤立的一两个。
    • 查看v$session_wait来确定wait是不是总是集中在一个特定的对象上(即:一个特定的P1RAW值)。
    • 使用v$sqlarea来确定是否有任何负载值很高的或者有反复的重载对对象EXCLUSIVE mode pin的请求。
    • 有时对发生等待的会话进行跟踪是很有帮助的

 

  • 减少Library Cache竞争的一般建议

下边会介绍一下解决不同竞争的不同的方法。但是,很多时候这些现象都是由于 SQL 语句的版本数造成的。如果你看到了任何跟 library cache 相关的竞争,应该立刻检查 AWR Report 确保没有版本数很高(比如几百)的 SQL 语句。Version_count

如果有高版本数的存在,请参见文档Troubleshooting High Version count解决。

 

  • 如何降低 library cache lock 等待

我们首先要确认的是 library cache 的竞争是整个系统层面的还是只发生在某个或某些 SQL 语句上。这个"library cache lock"是被一个特定的 SQL 持有很长的时间吗?或者总是在等待某个特定的对象?还是说这个锁在短时间内被请求的次数很多从而造成的竞争?

如果问题是在整个系统层面发生的,一般来说是由于 shared pool 太小或 SQL 语句不共享造成的。一些解决竞争的方法:

    • 增大 shared pool 从而减少 reload 的次数,这是因为 shared pool 过小会造成获取锁的时间加长。
    • 通过将 cursor_sharing 设置为 similar 或 force 来使 SQL 语句共享。(不建议,建议调整SQL使其共享)
      需要小心的是这样做可能会改变SQL的执行计划,所以做之前需要做完整的测试。
    • 在系统不繁忙的时候做统计信息的收集或其它维护作业,从而降低无效化(invalidation)的次数。

如果你发现是某条或某些SQL产生的问题,那么需要检查为什么它持有锁的时间会那么长。你可以在找出阻塞者章节中提供的方法来找到谁在持有锁以及在哪个对象上。

 

  • 如何降低 library cache pin 等待

怎么去降低这些等待依赖于正在发生的阻塞的严重性,通常情况下这些等待是由于使用了动态SQL的PL/SQL存储过程的PL/SQL代码被重编译,而这些动态SQL要调用的一些东西又依赖于这个正在调用的存储过程。

如果"library cache pin"等待的时间很严重,那么很重要的一点就是判断是只有一两个 process 在等待还是有很多的 process 都在等待。

    • 如果说只是一两个 process 被另一个 process 阻塞的话,那么需要检查持有这个 pin 的 process 为什么这么长时间不释放。
    • 如果说等待是大范围的那么说明 shared pool 需要优化。

 

  • 什么是 Library cache load lock?

这里再介绍另一个跟library cache有关的enqueue,library cache load lock。

Session 尝试去查找数据对象上的 load lock,以便能加载这个对象。

Load lock 一定是以排它模式获得,以便没有其它进程可以加载同一个对象。如果无法获得 load lock,那么 session 将等待这个事件,直到其变为可用状态。

等待时间: 3秒(PMON 会等待1秒)

参数:

    • object address

对象的地址

    • lock address

锁的地址

  • 如何减少 Library cache load lock

如果一个对象不在内存中,那么我们不能对其申请 library cache lock。

因此,需要将这个对象加载到内存中。

然后,session 尝试找到数据库对象的 load lock,以便它能载入这个对象。

为了阻止多进程同时请求加载同一个对象,其它同样请求的 session 将等待 library cache load lock 因为这个对象正在被加载到内存中。

等待 library cache load lock 是由于对象在内存中是不存在的。

Library cache 中的对象不存在,是由于 shared pool 过小引起的频繁重新装载,或太多的硬解析缘于不共享的 SQL。

避免这种等待的通常建议:

    • 增加 shared pool(避免 reload).
    • 增加 session cached cursors(避免 cursor 被刷出 shared pool)
    • 设置 cursor_sharing 为 force(减少硬解析)。---可能改变执行计划与查询的性能,所以要作充分的测试。(不建议)

 

  • Library cache pin 与 library load lock 是什么关系

Library cache pin 和 load lock 可能出现在对 PL/SQL, views, types 等的编译与重编译期间。这种编译总是显式的(比如应用安装,升级,打补丁)。但是对象重编译也可能发生在对象失效期间。

在处理那些“奇怪“的 library cache pin 和 load lock 等待时,我们需要检查为什么对象失效了。很有可能失效是由于某些操作修改了它所依赖的对象的"LAST_DDL"。通常来说这些操作包括对象维护操作,比如 ALTER, GRANT, REVOKE, replacing views 等等。还有就是收集 optimizer statistics 也会造成 cursor 失效,进而导致 library cache 的重装载。这个现象在 Oracle Server Application Developer's Guide 的object dependency maintenance 部分有描述。

对象失效以后,Oracle 尝试在第一次访问这个对象时去重编译它。有些情况下,如果其它 session 已经 pin 住这个对象,可能就会出现问题。 很显然,在有大量活跃用户与复杂依赖关系(例如,很多交叉依赖的 packages 或package bodies)的情况下更容易出现问题。有些时候对对象的编译会持续数小时从而阻止其它 session 对其的访问。

在 library cache dump, level 10 可以看到:

查找 ALTER ... COMPILE 语句和 lock=X 或 pin=X 的 objects/handles.

  • 提示
    • 需要频繁使用的 stored PL/SQL 所依赖的对象,对其 altering, granting, evoking 操作需要特别小心。实际上,解决这种问题很大程度上依赖于应用程序和系统维护的时间。应用程序开发者需要考虑某些决定可能会对应用程序的可扩展性及性能产生负面影响。
    • Load lock总是以排它模式获得的。
      如果 session load lock 繁忙,session 将一直等待,直到锁变成可用。

 

  • 下面我们来做个实验来模拟library cache lock和library cache pin的产生和处理

我们需要用一个测试用户来做这个实验,我们要用到sys.dbms_lock这个PL/SQL包,所以首先我们要用sys用户将执行这个包的权限赋予我们的测试用户,比如yxd:

使用yxd用户登录创建一个测试用的存储过程p :

在执行之前我们先确保环境中没有其他的library cache pin和library cache lock等待事件的存在,使用sys用户查看event

 

用yxd账户在会话A我们执行存储过程p :

再次使用sys用户查看event,这时没有library cache pin和library cache lock的等待出现

用yxd账户在会话B我们对存储过程p执行重编译操作,该操作会Hang在那里,因为锁不兼容,会产生等待事件

再次用sys用户查看event,这时比之前多出了两个事件,PL/SQL lock timer和library cache pin,PL/SQL lock timer这个事件是我们的存储过程p中执行的dbms_lock.sleep产生的,这是正常的,而library cache pin就是会话B产生的等待事件。

用yxd账户在会话C执行存储过程p :

再次用sys用户查看event,这时比之前多出了library cache lock事件

使用之前提到的SQL查找library cache lock的持有者和请求者

 

我们可以看到SID为68的会话对对象P持有了mode为3(X)的library cache lock,而SID为67的会话对对象P提出申请mode为2(S)的library cache lock,这两种锁不兼容,所以SID67的会话肯定会被SID为68的会话所阻塞,我们通过SID找到相应的SQL。

我们可以看到阻塞会话67的阻塞者会话68就是我们的会话B,但其实我们的会话B也被阻塞者无法完成SQL,既然会话B以X mode已经持有了对象P的library cache lock,那么它应该是在library cache pin上被其他会话阻塞,我们使用之前提到的SQL来查找library cache pin的持有者和请求者。

 

从结果上我们可以看出来,SID为58的会话以mode 2(S)持有了对象P的library cache pin,而SID为68的会话对对象P以mode 3(X)申请library cache pin,这两种锁是不兼容的,所以最终的阻塞者是SID为58的这个会话,SID58的会话就是我们的A会话,一般的情况下我们只要处理最终的阻塞者会话,将其KILL掉锁就释放了,如果要详细研究可以使用之前提到的方法对数据库进行SYSTEMSTATE和HANGANLYZE信息采集。

而且从这里我们可以清楚的看到,会话C其实对于对象P无论是library cache lock还是library cache pin它都会要申请为mode 2(S),因为它只是读取对象P,不做任何更改,从之前我们讨论了,在library cache pin中一个X(3)请求会被任何一个持有对象S(2)模式的pin所阻塞,一个S(2)请求会被任何一个持有对象X(3)模式的pin所阻塞,或者也许会队列到一些其他的X请求之后,所以在pin的阻塞中应该也要会看到会话C的pin的mode 2(S)的请求,但是为什么没有呢?其实很简单,因为所有的需要获得library cache pin的操作首先必须要先获取library cache lock,从实验中我们也看到了,会话C在请求library cache lock时被会话B阻塞了,它还没到获取library cache pin的那一步,所以它没有出现在library cache pin的阻塞队列中。

上面这种情况是在这个存储过程在会话中第一次执行的时候才会出现会话3被阻塞在获取library cache lock的阶段,而没有出现在library cache pin的阻塞队列中,但是如果之前已经执行过这个存储过程,情况又会不同,我们在之前的3个会话中将执行的SQL强行中断,然后再重新按照会话A执行存储过程P-->会话B对存储过程P重编译-->会话C执行存储过程P的顺序再操作一遍,执行查找lock的SQL的结果如下:

我们很清楚的看到,会话C对library cache lock的请求没有了,变成了以mode 1(NULL)持有library cache lock,之前我们提到过NULL是一种特殊的情况,它会一直保留在shared pool中,直到执行的SQL被刷出去才会消失,所以如果那个SQL还在内存中,再次执行时他会先以mode 1持有library cache lock,然后检查它是否正确有效,既然它持有了library cache lock它就可以进入到获取library cache pin的阶段,于是我们执行查找pin的阻塞队列时发现如下结果:

 

会话C进入了获取library cache pin的队列,有时还会出现同一个会话同时以mode 1(NULL)持有library cache lock和以mode 2(S)请求library cache lock。

请尊重我们的辛苦付出,未经允许,请不要转载 Ask600 的文章!