sql如何查看正在運(yùn)行的存儲(chǔ)過(guò)程

這篇文章將為大家詳細(xì)講解有關(guān)sql如何查看正在運(yùn)行的存儲(chǔ)過(guò)程，小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

最近項(xiàng)目一直在對(duì)表增加字段，很多失效對(duì)象需要編譯，經(jīng)常發(fā)現(xiàn)由于過(guò)程正在運(yùn)行因此導(dǎo)致編譯的會(huì)話HANG在那，直到過(guò)程運(yùn)行結(jié)束。
如果能有一個(gè)手段告訴我數(shù)據(jù)庫(kù)里有哪些過(guò)程正在運(yùn)行就好了，那么我們就可以選擇對(duì)這些過(guò)程依賴的表后加字段，避開(kāi)這個(gè)問(wèn)題。
可以通過(guò)以下查詢來(lái)定位到正在運(yùn)行的存儲(chǔ)過(guò)程：
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

查詢結(jié)果里的locks的輸出代表有n個(gè)會(huì)話持有該對(duì)象在library cache區(qū)域的library cache lock。
pins的輸出代表有n個(gè)會(huì)話持有該對(duì)象在library cache區(qū)域的library cache pin.
v$db_object_cache這個(gè)視圖里面的locks和pins代表對(duì)象上有多少個(gè)會(huì)話持有了該對(duì)象上上的library cache lock/pin。
但是并不能告訴你是哪個(gè)/些會(huì)話持有的，也不能告訴你持有的模式。如果僅僅是為了能夠順利編譯通過(guò)過(guò)程，知道上述信息也就夠了。
進(jìn)一步的，如果你想了解到有哪些會(huì)話正在執(zhí)行這個(gè)過(guò)程，那么還得費(fèi)點(diǎn)勁才性。
其實(shí)上面查詢語(yǔ)句的條件locks大于0不是必須的：
1）存儲(chǔ)過(guò)程的運(yùn)行過(guò)程中，library cache lock會(huì)加一個(gè)null的鎖，library cache pin會(huì)加一個(gè)s的鎖。
靠這個(gè)鎖來(lái)保護(hù)存儲(chǔ)過(guò)程運(yùn)行中代碼存儲(chǔ)的內(nèi)存HEAP不會(huì)被刷出去。如果在存儲(chǔ)過(guò)程運(yùn)行運(yùn)行過(guò)程中，你去編譯那么就會(huì)遭遇library cache pin等待
因?yàn)榫幾g的會(huì)話需要獲取x模式的library cache pin，這個(gè)x模式與執(zhí)行這個(gè)過(guò)程的會(huì)話持有的s模式不兼容而發(fā)生等待。
2）但是歷史上一個(gè)會(huì)話如果執(zhí)行某個(gè)過(guò)程的次數(shù)大于3次，那么這個(gè)會(huì)話也可能保留對(duì)這個(gè)library cache對(duì)象的null模式的library cache lock，
即使這個(gè)會(huì)話當(dāng)前沒(méi)有執(zhí)行這個(gè)過(guò)程也會(huì)保留這個(gè)null的library cache lock.對(duì)library cache pin不加任何鎖，這個(gè)功能是開(kāi)啟session_cached_cursors后的作用。
這個(gè)參數(shù)的作用當(dāng)然不僅僅限制與PL/SQL過(guò)程，對(duì)游標(biāo)依然如此、保留這個(gè)null的library cache lock的作用是，pga里保留了指向library cache對(duì)象的指針，下次解析
可以精確定位，不用在長(zhǎng)時(shí)間（相對(duì)的）的持有l(wèi)ibrary cache latch的情況下去hash bucket里去搜索了。
根據(jù)上面的論述我們可以知道，過(guò)程在執(zhí)行的話，pin一定要持有，過(guò)程不執(zhí)行pin一定不持有（編譯持有時(shí)間極端，我們可以不考慮），那么pins>0就可以代表了這個(gè)
過(guò)程有n個(gè)會(huì)話在運(yù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
可以看到過(guò)程上有很多會(huì)話保留了library cache lock，根據(jù)我前面的描述，能夠知道這個(gè)鎖模式是NULL的模式，但是由于過(guò)程沒(méi)在運(yùn)行，library cache pin沒(méi)加鎖，pins等于0.
下面看下如何找到哪個(gè)/些會(huì)話在執(zhí)行過(guò)程？既然在執(zhí)行就代表這個(gè)過(guò)程的游標(biāo)是打開(kāi)的，我們可以看看v$open_cursor這個(gè)視圖。
在寫這篇博文之前，我沒(méi)有意識(shí)到查找哪些會(huì)話在正在執(zhí)行某個(gè)過(guò)程會(huì)是這么的艱難。
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個(gè)進(jìn)程正在運(yùn)行這個(gè)過(guò)程。locks為4代表有4個(gè)會(huì)話持有了這個(gè)過(guò)程上null模式的library cache lock，還能推測(cè)出其中有3個(gè)locks是
這些會(huì)話歷史執(zhí)行過(guò)這個(gè)過(guò)程，當(dāng)前已經(jīng)不再運(yùn)行了。
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，我們雖然能夠得到執(zhí)行這個(gè)過(guò)程的sid，但是不難發(fā)現(xiàn)我們查詢的結(jié)果顯示的是4條記錄，也就是說(shuō)這個(gè)視圖會(huì)把當(dāng)前游標(biāo)處于
open狀態(tài)的都顯示出來(lái)，這里面只有一個(gè)會(huì)話是正在執(zhí)行我們關(guān)注的過(guò)程。這個(gè)時(shí)候我們可以借助v$session來(lái)查看這些會(huì)話當(dāng)前在執(zhí)行哪些sql來(lái)判定
如果執(zhí)行的sql包含在我們關(guān)注的過(guò)程里，那么就能定位到執(zhí)行我們關(guān)注的過(guò)程的會(huì)話。
@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 8vtas2njh5t3c      369
   1835 1085950    db file sequential read              23      58000          1 faywn3b7f7p19      0
   1851 1606066    db file sequential read             109     630082          1 anfr2phncqn6t      603

可以看到只有1851有非空閑等待，它的操作系統(tǒng)進(jìn)程號(hào)是1606066，當(dāng)前正運(yùn)行anfr2phncqn6t（sql_id)這個(gè)語(yǔ)句，最終定位到這個(gè)sql是我們存儲(chǔ)過(guò)程里的。而其他3個(gè)會(huì)話處于空閑狀態(tài)。
看來(lái)在oracle里定位某個(gè)過(guò)程正在被哪些會(huì)話所執(zhí)行并不是一件容易的事。其實(shí)整個(gè)過(guò)程熟悉后，定位起來(lái)也不麻煩。

附帶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
/

關(guān)于“sql如何查看正在運(yùn)行的存儲(chǔ)過(guò)程”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。