[i=s] 本帖最後由 wei-xh 於 2012-7-28 20:43 編輯

最近專案一直在對錶增加欄位，很多失效物件需要編譯，經常發現由於過程正在執行因此導致編譯的會話HANG在那，直到過程執行結束。
如果能有一個手段告訴我資料庫裡有哪些過程正在執行就好了，那麼我們就可以選擇對這些過程依賴的表後加欄位，避開這個問題。
可以透過以下查詢來定位到正在執行的儲存過程：
col name for a40
select name,locks,pins
from v$db_object_cache
where locks > 0 and pins > 0 and type='PROCEDURE';

NAME                                        LOCKS    PINS
---------------------------------------- ---------- ----------
P_GLOBAL_ACCOUNT_SM                            1       1
PRO_SERVICE_MONITOR_VAS                         4       1
BUILD_ORD_ORDER_SEARCH_PRO                      23       1

查詢結果裡的locks的輸出代表有n個會話持有該物件在library cache區域的library cache lock。
pins的輸出代表有n個會話持有該物件在library cache區域的library cache pin.
v$db_object_cache這個檢視裡面的locks和pins代表物件上有多少個會話持有了該物件上上的library cache lock/pin。
但是並不能告訴你是哪個/些會話持有的，也不能告訴你持有的模式。如果僅僅是為了能夠順利編譯透過過程，知道上述資訊也就夠了。
進一步的，如果你想了解到有哪些會話正在執行這個過程，那麼還得費點勁才性。
其實上面查詢語句的條件locks大於0不是必須的：
1）儲存過程的執行過程中，library cache lock會加一個null的鎖，library cache pin會加一個s的鎖。
靠這個鎖來保護儲存過程執行中程式碼儲存的記憶體HEAP不會被刷出去。如果在儲存過程執行執行過程中，你去編譯那麼就會遭遇library cache pin等待
因為編譯的會話需要獲取x模式的library cache pin，這個x模式與執行這個過程的會話持有的s模式不相容而發生等待。
2）但是歷史上一個會話如果執行某個過程的次數大於3次，那麼這個會話也可能保留對這個library cache物件的null模式的library cache lock，
即使這個會話當前沒有執行這個過程也會保留這個null的library cache lock.對library cache pin不加任何鎖，這個功能是開啟session_cached_cursors後的作用。
這個引數的作用當然不僅僅限制與PL/SQL過程，對遊標依然如此、保留這個null的library cache lock的作用是，pga裡保留了指向library cache物件的指標，下次解析
可以精確定位，不用在長時間（相對的）的持有library cache latch的情況下去hash bucket裡去搜尋了。
根據上面的論述我們可以知道，過程在執行的話，pin一定要持有，過程不執行pin一定不持有（編譯持有時間極端，我們可以不考慮），那麼pins>0就可以代表了這個
過程有n個會話在執行它了，n的值等於pins的值。

select  name,locks,pins
from v$db_object_cache
  where type='PROCEDURE' and rownum<10;

NAME                                        LOCKS    PINS
---------------------------------------- ---------- ----------
P_GLOBAL_ACCOUNT_SM                            0       0
BUILD_BUFFER                                  11       0
BUILD_BUFFER                                  11       0
可以看到過程上有很多會話保留了library cache lock，根據我前面的描述，能夠知道這個鎖模式是NULL的模式，但是由於過程沒在執行，library cache pin沒加鎖，pins等於0.
下面看下如何找到哪個/些會話在執行過程？既然在執行就代表這個過程的遊標是開啟的，我們可以看看v$open_cursor這個檢視。
在寫這篇博文之前，我沒有意識到查詢哪些會話在正在執行某個過程會是這麼的艱難。
col name for a40
select name,locks,pins
from v$db_object_cache
where locks > 0 and pins > 0 and type='PROCEDURE';

NAME                                        LOCKS    PINS
---------------------------------------- ---------- ----------
TMP_PREPARE_SYNC_DATA                            4       1
pins為1代表有1個程式正在執行這個過程。locks為4代表有4個會話持有了這個過程上null模式的library cache lock，還能推測出其中有3個locks是
這些會話歷史執行過這個過程，當前已經不再執行了。
select sid,sql_text from v$open_cursor where sql_text like '%tmp_prepare_sy%' and user_name='RETL_RPT';

   SID SQL_TEXT
---------- ------------------------------------------------------------
   2142 call RETL_RPT.tmp_prepare_sync_data()
   1880 call RETL_RPT.tmp_prepare_sync_data()
   2107 call RETL_RPT.tmp_prepare_sync_data()
   1851 call RETL_RPT.tmp_prepare_sync_data()
可惜檢視v$open_cursor，我們雖然能夠得到執行這個過程的sid，但是不難發現我們查詢的結果顯示的是4條記錄，也就是說這個檢視會把當前遊標處於
open狀態的都顯示出來，這裡面只有一個會話是正在執行我們關注的過程。這個時候我們可以藉助v$session來檢視這些會話當前在執行哪些sql來判定
如果執行的sql包含在我們關注的過程裡，那麼就能定位到執行我們關注的過程的會話。
@active

  SID SPID    EVENT                               P1       P2       P3 SQL_ID          SECON
------- ---------- ------------------------- ------------- ---------- ---------- ------------------ -----
1428 1130998 SQL*Net message from dbli    675562835       1       0 bzrggnv5fqp7x    304
1517 2314552 SQL*Net message to client 1650815232       1       0 3t37hp1cnkuux    0
1801 2126202 db file scattered read             27    93442       16 a5s8306j8a699    1
1849 405924    db file scattered read             142    476281       7 2zvv5wpg7qajb    70
1644 1761680 db file sequential read          318    446010       1 4xk36k7z79fpj    10
1737 1663014 db file sequential read             62    180837       1 536qa75pznr0z    8
1804 1302550 db file sequential read          278    341240       1 8vtas2njh4t3c    369
1835 1085950 db file sequential read             23    58000       1 faywn3b7f7p19    0
1851 1606066 db file sequential read          109    630082       1 anfr2phncqn6t    603

可以看到只有1851有非空閒等待，它的作業系統程式號是1606066，當前正執行anfr2phncqn6t（sql_id)這個語句，最終定位到這個sql是我們儲存過程裡的。而其他3個會話處於空閒狀態。
看來在oracle裡定位某個過程正在被哪些會話所執行並不是一件容易的事。其實整個過程熟悉後，定位起來也不麻煩。

附帶active指令碼如下：
select /*+use_nl(a,b,c)*/distinct a.sid,a.serial#,a.username,a.terminal,a.machine,a.program,b.spid,c.sql_id,c.sql_text as sql_text1
from v$session a,v$process b,v$sql c
where a.paddr = b.addr(+)
and a.sql_hash_value = c.hash_value
and a.sql_address = c.address
and a.status = 'ACTIVE'
and a.type = 'USER'
/

select /*+ ordered use_nl(a,b) */a.sid as sid,b.spid as spid,substr(c.event,1,25) as event,c.p1,c.p2,c.p3,trim(to_char(a.sql_id)) as sql_id,to_char(LAST_CALL_ET) as seconds
from v$session a,v$process b,v$session_wait c
where a.type = 'USER' and a.status = 'ACTIVE'
and a.paddr = b.addr
and a.sid = c.sid
and a.wait_class <> 'Idle'
order by event
/

檢視正在執行的儲存過程

相關文章