一次library cache pin故障的解决过程

今天接到同事的电话,说他的一个存储过程已经run了一个多小时了,还在继续run,他觉得极不正常,按道理说不应该run这么长时间。

我说那我去看一下吧。

 

这个库是一个AIX上的10.2.0.4,我采集了一下问题时间段的AWR报告:

Begin Snap:

13302

11-Jun-10 12:00:56

109

4.7

End Snap:

13303

11-Jun-10 13:00:02

97

4.9

Elapsed:

 

59.10 (mins)

 

 

DB Time:

 

113.98 (mins)

 

 

 

Top 5事件为:

Event

Waits

Time(s)

Avg Wait(ms)

% Total Call Time

Wait Class

library cache pin

1,252

3,656

2,920

53.5

Concurrency

library cache lock

989

2,890

2,922

42.3

Concurrency

CPU time

 

219

 

3.2

 

db file sequential read

5,694

12

2

.2

User I/O

log file parallel write

1,467

11

8

.2

System I/O

 

AWR报告结果里我们可以看出在出问题的时间段,系统在经历严重的library cache pin以及library cache lock等待。

 

Load Profile中我们又可以看到:

 

Per Second

Per Transaction

Parses:

12.83

65.83

Hard parses:

0.05

0.25

也就是说导致上述library cache pinlibrary cache lock的并不是hard parse

 

对于library cache pin等待来说,AWR报告的作用有限,最有效的方式就是找到持有library cache pin以及等待library cache pinsession,然后看看他们在做什么

 

SQL> SELECT s.sid, kglpnmod “Mode”, kglpnreq “Req”, SPID “OS Process”

  2  FROM v$session_wait w, x$kglpn p, v$session s ,v$process o

  3  WHERE p.kglpnuse=s.saddr

  4  AND kglpnhdl=w.p1raw

  5  and w.event like ‘%library cache pin%’

  6  and s.paddr=o.addr

  7  /

 

       SID       Mode        Req OS Process

———- ———- ———- ————

       396          0          2 6381970

       396          0          2 6381970

       396          0          2 6381970

       396          0          2 6381970

       341          2          0 4092132

       341          2          0 4092132

       341          2          0 4092132

       341          2          0 4092132

       363          0          2 3514690

       363          0          2 3514690

       363          0          2 3514690

       363          0          2 3514690

       304          0          2 3977478

       304          0          2 3977478

       304          0          2 3977478

       304          0          2 3977478

       354          0          3 3137874

       354          0          3 3137874

       354          0          3 3137874

       354          0          3 3137874

 

20 rows selected

 

我那位run存储过程的同事所在的session396,从上述结果里我们可以看出来396现在想以Share模式(即Req=2)去持有library cache pin,同时现在持有上述library cache pin的是session 341,且341的持有模式也是Share(即Mode=2)。

本来ShareShare是可以共享的,但不幸的是在396之前,session 354想以Exclusive模式(即Req=3)去持有上述library cache pin,这直接导致了396需要处于等待的Queue中,同时处于Queue中的还有363304

 

我为什么这么说呢,因为oraclelibrary cache pin的解释中有一句非常经典的话:

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.

 

所以从AWR报告和上述查询结果中我们可以得出如下结论:

1、  我那位run存储过程的同事为什么run1个多小时还没有run完是因为这个存储过程正在经历严重的library cache pin等待;

2、  而为什么会导致严重的library cache pin等待是因为session 341354联手达到了这一效果,即341Share模式持有library cache pin,接着354想以Exclusive模式持有,这直接导致所有的后续请求全部被处于等待的Queue中。也就是说341阻塞了354,而354又间接阻塞了396

 

既然知道了原因,那我们去看一下session 341在做什么事情:

SQL> select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid=341;

 

DECODE(SQL_HASH_VALUE,0,PREV_H

——————————

                     784727971

 

间隔10秒钟后再次执行:

SQL> select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid=341;

 

DECODE(SQL_HASH_VALUE,0,PREV_H

——————————

                     784727971

 

间隔10秒钟后再次执行:

SQL> select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid=341;

 

DECODE(SQL_HASH_VALUE,0,PREV_H

——————————

                     784727971

 

SQL> select sql_text from v$sqltext where hash_value=784727971 order by piece;

 

SQL_TEXT

—————————————————————-

begin   — Call the procedure   p_adj_rrp_main(o_vc_flag => :o_vc_flag); end;

 

从结果里可以看到341一直在run一个存储过程。

给持有341的那位大姐打电话,问她在做什么,她告诉我说她从昨晚就开始run这个存储过程,今早来看发现死掉了,所以她就没管了。

 

知道原因后处理起来还是很容易的,当我把session 341干掉后,整个系统的library cache pin一下子就降下来了,接着我那位同事的run了一个多小时的存储过程过了没多久就run完了。


8 Comments on “一次library cache pin故障的解决过程”

  1. Kamus says:

    哥,都10g了,SQL里还加什么v$session_wait视图呢?

  2. Asher says:

    与本博文无关的一个问题:右上角为什么不是Articles Index ?

  3. htyansp says:

    大师:

    SESSION 354 以x模式去请求libary cache pin

    这个354想干嘛?

    编译存储过程?

  4. ivy says:

    如果是在RAC里面,诊断的脚本应该是不能看到所有library pin 的持有者或等待者


Leave a Reply

Your email address will not be published. Required fields are marked *