Oracle grant 授權 出現 library cache lock 等待事件 處理

 

 

同事下午做grant 賦權操作，執行如下命令中掛住，等了N長時間也沒有執行完畢：

SQL> grant select on UR_USER_INFO tohfyd;

 

查看了一下等待事件，是librarycache lock。

 

OracleLibrary Cache Lock 解決思路

http://blog.csdn.net/tianlesoftware/article/details/7956996

 

Library Cache lock有3中模式：

       （1）Share(S):     當讀取一個library cache object的時候獲得

       （2）Exclusive(X): 當建立/修改一個library cacheobject的時候獲得

       （3）Null(N)：     用來確保對象依賴性

 

       比如一個進程想要編譯某個視圖，那麼就會獲得一個共用鎖定，如果我們要create/drop/alter某個對象，那麼就會獲得exclusive lock。Null鎖非常特殊，我們在任何可以執行的對象(cursor，function)上面都擁有NULL鎖，我們可以隨時打破這個NULL鎖，當這個NULL鎖被打破了,就表示這個對象被更改了，需要從新編譯。

       NULL鎖主要的目的就是標記某個對象是否有效。比如一個SQL語句在解析的時候獲得了NULL 鎖，如果這個SQL的對象一直在共用池中，那麼這個NULL鎖就會一直存在下去，當這個SQL語句所引用的表被修改之後，這個NULL鎖就被打破了，因為修改這個SQL語句的時候會獲得Exclusive 鎖，由於NULL鎖被打破了，下次執行這個SQL的時候就需要從新編譯。

 

 

 

等待事件如下：

EVENT                                                             COUNT(*)

--------------------------------------------------------------------------

jobq slave wait                                                          1

SQL*Net more data from dblink                                             1

db file sequential read                                                  2

SQL*Net message to client                                                1

db file scattered read                                                   1

SQL*Net break/reset to client                                             1

SQL*Net message from client                                            238

smon timer                                                               1

pmon timer                                                               1

SQL*Net message fromdblink                                              4

rdbms ipc message                                                       20

Streams AQ: qmn slave idle wait                                           1

library cache lock                                                       1

Streams AQ: qmn coordinator idle wait                                     1

SQL*Net more data to client                                              1

Streams AQ: waiting for time management orcleanup tasks                  1

 

這裡標紅了2個地方，後面會具體分析。

 

 

 

SQL>  select decode(request, 0, 'Holder:', ' --Waiter:') || s.inst_id || ':'||

 2         s.sid || ',' ||s.serial# as sess,

 3         l.id1,

 4         l.id2,

 5         l.lmode,

 6         l.request,

 7         l.type,

 8         l.ctime,

 9         s.sql_id,

 10        s.event,

 11        s.last_call_et

 12   from gv$lock l, gv$session s

 13  where (id1, id2, l.type) in

 14        (select id1, id2, type from gv$lock where request > 0)

 15    and l.sid = s.sid

 16    and l.inst_id = s.inst_id order by id1,ctime desc,request;

 

no rows selected

 

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

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

 3  WHERE p.kglpnuse=s.saddr

 4  AND kglpnhdl=w.p1raw

 5  and w.event like '%librarycache lock%'

 6  and s.paddr=o.addr

 7  /

 

      SID       Mode        Req OS Process

---------- ---------- ----------------------

      5444          2          0 18645

 

第二SQL 返回了一個對應的session： 5444。 這裡的Mode 2是share模式，如果是3就是Exclusive模式。

 

進一步查看：

 

oracle@w25k03da$ora active|grep library

 5261  1167 4296         SYS        w25k03da         library cachelock             66552486656/616245    0 b84sn8gh70cx7

 

這裡的session ：5261 是我們執行grant的語句，其對應的等待事件是library cache lock ，該操作已經掛了很長時間。

 

看一下session： 5444.

oracle@w25k03da$ora active|grep 5444

 5444   130 18645        BZYD       WORKGROUP\BZYD-7 SQL*Net message from dblink   1413697536/1/0        0328u7k7c5mycu

 

oracle@w25k03da$ps -ef|grep 18645

 oracle 18645     1   0 07:18:49 ?           0:00 oraclewcrma1 (LOCAL=NO)

 oracle  8062  8059  0 17:40:39 pts/2       0:00 grep18645

 

該session 是一個遠端串連。

 

這裡我們做一個hanganalyze 分析，就清楚他們之間到底是什麼關係了：

 

Hanganalyze的用法如下：

$ sqlplus -prelim / as sysdba

SQL>set time on

18:02:57SQL> set timing on

SQL>oradebug setmypid

Statementprocessed.

SQL>alter session set tracefile_identifier='dave';

SQL>oradebugunlimit

SQL>oradebug hanganalyze 3

HangAnalysis in /oracle/admin/bomc/udump/bomc2_ora_1011948.trc

 

從trace 檔案裡我們取出關鍵內容：

State of nodes

([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):

…

[5259]/0/5260/195/0x77d77060/5879/IGN/105/106//none

[5260]/0/5261/1167/0x76da5ea8/4296/NLEAF/107/110/[5443]/none

[5261]/0/5262/78/0x75d9c660/4332/IGN/111/112//none

…

[5442]/0/5443/124/0x76dc8b38/1814/IGN/453/454//none

[5443]/0/5444/130/0x75dbf2f0/18645/LEAF/108/109//5260

[5444]/0/5445/218/0x7fda5440/10872/IGN/455/456//none

…

 

從這裡，我們可以確定：[5443]阻塞了[5260]。導致我們的grant操作一直擷取到不到library cache的lock，而處於等待狀態。

 

[5260] 是我們的grant 操作，對應session：5261。

[5443] 對應的就是我們看到的session：5444。

 

 

查看session： 5444：

SQL> selectsid,username,status,osuser,machine,program,serial#,terminal from v$sessionwhere sid='5444';

 

      SID USERNAME                      STATUS   OSUSER                         MACHINE         PROGRAM            SERIAL# TERMINAL

---------- -------------------------------------- ------------------------------ --------------- ------------------------- ------------------------------

     5444 BZYD                          ACTIVE   Administrator                  WORKGROUP\BZYD- sqlplus.exe            130 BZYD-7D54F1F8A0

                                                                                 7D54F1F8A0

 

這個是一個個人終端的sqlplus，查看了一下連線時間，早上7：19分串連的，後來一直掛在這。

 

根據sql_id，從V$SQLTEXT中沒有撈到對應的SQL. 同事想了半天，也沒能確認這個串連是誰的，無耐把這個session kill 掉了，grant 操作立馬完成。

 

 

現在想想，應該從dba_hist_sqltext在撈一把，或許就撈到那個SQL了。語句如下：

select sql_text from dba_hist_sqltext wheresql_id='aysann58c69s3';

 

 

發現library cache 相關的問題很多，前段時間也是剛處理過一個因為library cache導致資料庫掛住的故障，參考：

Oraclelatch:library cache 導致 資料庫掛起 故障

http://blog.csdn.net/tianlesoftware/article/details/8112966

 

 

其他的一些有關library cache 的說明：

一次library cache pin故障的解決過程

http://blog.csdn.net/tianlesoftware/article/details/6638899

 

OracleLibrary cache 內部機制 說明

http://blog.csdn.net/tianlesoftware/article/details/6629869

 

OracleLibrary Cache 的 lock 與 pin 說明

http://blog.csdn.net/tianlesoftware/article/details/6641440

 

 

 

 

 

 

---------------------------------------------------------------------------------------

著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!

Skype:    tianlesoftware

QQ:       tianlesoftware@gmail.com

Email:    tianlesoftware@gmail.com

Blog:     http://blog.csdn.net/tianlesoftware

Weibo:    http://weibo.com/tianlesoftware

Twitter:  http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware