Oracle Study之--Oracle等待事件（8）

Oracle Study之--Oracle等待事件（8）

     庫(kù)緩存中的對(duì)象在庫(kù)緩存中被切割成多個(gè)內(nèi)存塊，另有一個(gè)對(duì)象句柄記錄了各個(gè)內(nèi)存塊的地址和其他的一些信息。當(dāng)你要修改句柄中的信息時(shí)，需要在句柄上加獨(dú)占鎖，而如果另一個(gè)進(jìn)程恰好在這時(shí)要求讀、寫句柄中的信息，它就必須等待。此時(shí)的等待就被Oracle記入Library cache lock事件。而讀、寫對(duì)象內(nèi)存塊也是無(wú)法同時(shí)進(jìn)行的，有人如果正在寫，你的讀操作就必須等待，讀寫內(nèi)存塊的等待事件就是Library cache pin。如果這兩個(gè)等待事件過(guò)多，同樣說(shuō)明了庫(kù)緩存過(guò)小或沒(méi)有共享執(zhí)行計(jì)劃?；蛘撸?dāng)你在數(shù)據(jù)庫(kù)繁忙時(shí)使用DDL時(shí)，也會(huì)有這兩個(gè)等待事件。

Library cache lock
這個(gè)等待事件發(fā)生在不同用戶在共享中由于并發(fā)操作同一個(gè)數(shù)據(jù)庫(kù)對(duì)象導(dǎo)致的資源爭(zhēng)用的時(shí)候，比如當(dāng)一個(gè)用戶正在對(duì)一個(gè)表做DDL 操作時(shí)，其他的用戶如果要訪問(wèn)這張表，就會(huì)發(fā)生library cache lock等待事件，它要一直等到DDL操作完成后，才能繼續(xù)操作。
這個(gè)事件包含四個(gè)參數(shù)：
Handle address: 被加載的對(duì)象的地址。
Lock address： 鎖的地址。
Mode： 被加載對(duì)象的數(shù)據(jù)片段。
Namespace： 被加載對(duì)象在v$db_object_cache 視圖中namespace名稱。
10gr2 rac:

sys@ORCL> select name from v$event_name where name like 'library%' order by 1;
NAME
--------------------------------------------------
library cache load lock
library cache lock
library cache pin
library cache revalidation
library cache shutdown

Library cache pin
這個(gè)等待事件和library cache lock 一樣是發(fā)生在共享池中并發(fā)操作引起的事件。通常來(lái)講，如果Oracle 要對(duì)一些PL/SQL 或者視圖這樣的對(duì)象做重新編譯，需要將這些對(duì)象pin到共享池中。如果此時(shí)這個(gè)對(duì)象被其他的用戶特有，就會(huì)產(chǎn)生一個(gè)library cache pin的等待。
這個(gè)等待事件也包含四個(gè)參數(shù)：
Handle address: 被加載的對(duì)象的地址。
Lock address： 鎖的地址。
Mode： 被加載對(duì)象的數(shù)據(jù)片段。
Namespace： 被加載對(duì)象在v$db_object_cache 視圖中namespace名稱。

案例分析：

12:03:15 SCOTT@ prod>begin
12:03:38   2    for i in 1..100000 loop
  execute immediate 'insert into t1 values ('||i||')';
12:03:38   3  12:03:38   4    end loop;
12:03:38   5    end;
12:03:39   6    /
11:25:39 TOM@ prod>begin
12:03:43   2    for i in 1..100000 loop
12:03:43   3    execute immediate 'insert into t1 values ('||i||')';
12:03:43   4    end loop;
12:03:43   5    end;
12:03:43   6    /
12:04:33 SYS@ prod>r
  1  select event,TOTAL_WAITS,AVERAGE_WAIT,EVENT_ID from v$system_event
  2* where event like '%lib%'
EVENT                                                            TOTAL_WAITS AVERAGE_WAIT   EVENT_ID
---------------------------------------------------------------- ----------- ------------ ----------
library cache load lock                                                    5         17.8 2952162927
library cache: mutex X                                                   142          .27 1646780882
Elapsed: 00:00:00.03

查詢語(yǔ)句：

方法一、只能查詢librarycache pin相關(guān)信息

SQL> SELECT distinct decode(kglpnreq,0,'holding_session:'||s.sid,'waiting_session: '||s.sid) sid,
2 s.SERIAL#, kglpnmod "Pin Mode", kglpnreq "ReqPin",a.sql_text,kglnaown "Owner", kglnaobj "Object"
3 FROM x$kglpn p, v$session s,v$sqlarea a,v$session_wait sw,x$kglob x
4 WHERE p.kglpnuse=s.saddr
5 AND kglpnhdl=sw.p1raw
6 and kglhdadr=sw.p1raw
7 and event like 'library cache%'
8 and (a.hash_value, a.address) IN (
9 select
10 DECODE (sql_hash_value,
11 0,
12 prev_hash_value,
13 sql_hash_value
14 ),
15 DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
16 from v$session s2
17 where s2.sid=s.sid
18 )
19 ;

方法二、可以查詢library cache pin和library cache lock 的信息

select  Distinct /*+ ordered */ w1.sid waiting_session,
h2.sid holding_session, 
w.kgllktype lock_or_pin, 
od.to_owner object_owner, 
od.to_name object_name, 
oc.Type, 
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 
'Unknown') mode_held, 
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 
'Unknown') mode_requested,
xw.KGLNAOBJ wait_sql,xh.KGLNAOBJ hold_sql 
from dba_kgllock w, dba_kgllock h, v$session w1, 
v$session h2,v$object_dependency od,V$DB_OBJECT_CACHE oc,x$kgllk xw,x$kgllk xh
where 
(((h.kgllkmod != 0) and (h.kgllkmod != 1) 
and ((h.kgllkreq = 0) or (h.kgllkreq = 1))) 
and 
(((w.kgllkmod = 0) or (w.kgllkmod= 1)) 
and ((w.kgllkreq != 0) and (w.kgllkreq != 1)))) 
and w.kgllktype = h.kgllktype 
and w.kgllkhdl = h.kgllkhdl 
and w.kgllkuse = w1.saddr 
and h.kgllkuse = h2.saddr 
And od.to_address = w.kgllkhdl 
And od.to_name=oc.Name 
And od.to_owner=oc.owner 
And w1.sid=xw.KGLLKSNM
And h2.sid=xh.KGLLKSNM 
And (w1.SQL_ADDRESS=xw.KGLHDPAR And w1.SQL_HASH_VALUE=xw.KGLNAHSH)
And (h2.SQL_ADDRESS=xh.KGLHDPAR And h2.SQL_HASH_VALUE=xh.KGLNAHSH) 
;

附注：

生產(chǎn)環(huán)境案例分析：（轉(zhuǎn)自：http://www.itpub.net/thread-1504538-1-1.html）

 今天接到同事的電話，說(shuō)他的一個(gè)存儲(chǔ)過(guò)程已經(jīng)run了一個(gè)多小時(shí)了，還在繼續(xù)run，他覺(jué)得極不正常，按道理說(shuō)不應(yīng)該run這么長(zhǎng)時(shí)間。

我說(shuō)那我去看一下吧。

這個(gè)庫(kù)是一個(gè)AIX上的10.2.0.4，我采集了一下問(wèn)題時(shí)間段的AWR報(bào)告：

Top 5事件為：

從AWR報(bào)告結(jié)果里我們可以看出在出問(wèn)題的時(shí)間段，系統(tǒng)在經(jīng)歷嚴(yán)重的library cache pin以及l(fā)ibrary cache lock等待。

從Load Profile中我們又可以看到：

也就是說(shuō)導(dǎo)致上述library cache pin和library cache lock的并不是hard parse。

對(duì)于library cache pin等待來(lái)說(shuō)，AWR報(bào)告的作用有限，最有效的方式就是找到持有l(wèi)ibrary cache pin以及等待library cache pin的session，然后看看他們?cè)谧鍪裁?/span>：

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存儲(chǔ)過(guò)程的同事所在的session是396，從上述結(jié)果里我們可以看出來(lái)396現(xiàn)在想以Share模式（即Req=2）去持有l(wèi)ibrary cache pin，同時(shí)現(xiàn)在持有上述library cache pin的是session 341，且341的持有模式也是Share（即Mode=2）。

本來(lái)Share和Share是可以共享的，但不幸的是在396之前，session 354想以Exclusive模式（即Req=3）去持有上述library cache pin，這直接導(dǎo)致了396需要處于等待的Queue中，同時(shí)處于Queue中的還有363和304。

我為什么這么說(shuō)呢，因?yàn)閛racle對(duì)library cache pin的解釋中有一句非常經(jīng)典的話：

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報(bào)告和上述查詢結(jié)果中我們可以得出如下結(jié)論：

1、  我那位run存儲(chǔ)過(guò)程的同事為什么run了1個(gè)多小時(shí)還沒(méi)有run完是因?yàn)檫@個(gè)存儲(chǔ)過(guò)程正在經(jīng)歷嚴(yán)重的library cache pin等待；

2、  而為什么會(huì)導(dǎo)致嚴(yán)重的library cache pin等待是因?yàn)閟ession 341和354聯(lián)手達(dá)到了這一效果，即341以Share模式持有l(wèi)ibrary cache pin，接著354想以Exclusive模式持有，這直接導(dǎo)致所有的后續(xù)請(qǐng)求全部被處于等待的Queue中。也就是說(shuō)341阻塞了354，而354又間接阻塞了396。

既然知道了原因，那我們?nèi)タ匆幌聅ession 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秒鐘后再次執(zhí)行：

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秒鐘后再次執(zhí)行：

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 => _vc_flag); end;

從結(jié)果里可以看到341一直在run一個(gè)存儲(chǔ)過(guò)程。

給持有341的那位大姐打電話，問(wèn)她在做什么，她告訴我說(shuō)她從昨晚就開(kāi)始run這個(gè)存儲(chǔ)過(guò)程，今早來(lái)看發(fā)現(xiàn)死掉了，所以她就沒(méi)管了。

知道原因后處理起來(lái)還是很容易的，當(dāng)我把session 341干掉后，整個(gè)系統(tǒng)的library cache pin一下子就降下來(lái)了，接著我那位同事的run了一個(gè)多小時(shí)的存儲(chǔ)過(guò)程過(guò)了沒(méi)多久就run完了。