這篇文章將為大家詳細(xì)講解有關(guān)sql如何查看正在運(yùn)行的存儲過程，小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。
創(chuàng)新互聯(lián)于2013年創(chuàng)立，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì)網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢想脫穎而出為使命，1280元上街做網(wǎng)站,已為上家服務(wù),為上街各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話:13518219792
最近項(xiàng)目一直在對表增加字段，很多失效對象需要編譯，經(jīng)常發(fā)現(xiàn)由于過程正在運(yùn)行因此導(dǎo)致編譯的會話HANG在那，直到過程運(yùn)行結(jié)束。
如果能有一個(gè)手段告訴我數(shù)據(jù)庫里有哪些過程正在運(yùn)行就好了，那么我們就可以選擇對這些過程依賴的表后加字段，避開這個(gè)問題。
可以通過以下查詢來定位到正在運(yùn)行的存儲過程：
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è)會話持有該對象在library cache區(qū)域的library cache lock。
pins的輸出代表有n個(gè)會話持有該對象在library cache區(qū)域的library cache pin.
v$db_object_cache這個(gè)視圖里面的locks和pins代表對象上有多少個(gè)會話持有了該對象上上的library cache lock/pin。
但是并不能告訴你是哪個(gè)/些會話持有的，也不能告訴你持有的模式。如果僅僅是為了能夠順利編譯通過過程，知道上述信息也就夠了。
進(jìn)一步的，如果你想了解到有哪些會話正在執(zhí)行這個(gè)過程，那么還得費(fèi)點(diǎn)勁才性。
其實(shí)上面查詢語句的條件locks大于0不是必須的：
1）存儲過程的運(yùn)行過程中，library cache lock會加一個(gè)null的鎖，library cache pin會加一個(gè)s的鎖。
靠這個(gè)鎖來保護(hù)存儲過程運(yùn)行中代碼存儲的內(nèi)存HEAP不會被刷出去。如果在存儲過程運(yùn)行運(yùn)行過程中，你去編譯那么就會遭遇library cache pin等待
因?yàn)榫幾g的會話需要獲取x模式的library cache pin，這個(gè)x模式與執(zhí)行這個(gè)過程的會話持有的s模式不兼容而發(fā)生等待。
2）但是歷史上一個(gè)會話如果執(zhí)行某個(gè)過程的次數(shù)大于3次，那么這個(gè)會話也可能保留對這個(gè)library cache對象的null模式的library cache lock，
即使這個(gè)會話當(dāng)前沒有執(zhí)行這個(gè)過程也會保留這個(gè)null的library cache lock.對library cache pin不加任何鎖，這個(gè)功能是開啟session_cached_cursors后的作用。
這個(gè)參數(shù)的作用當(dāng)然不僅僅限制與PL/SQL過程，對游標(biāo)依然如此、保留這個(gè)null的library cache lock的作用是，pga里保留了指向library cache對象的指針，下次解析
可以精確定位，不用在長時(shí)間（相對的）的持有l(wèi)ibrary cache latch的情況下去hash bucket里去搜索了。
根據(jù)上面的論述我們可以知道，過程在執(zhí)行的話，pin一定要持有，過程不執(zhí)行pin一定不持有（編譯持有時(shí)間極端，我們可以不考慮），那么pins>0就可以代表了這個(gè)
過程有n個(gè)會話在運(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
可以看到過程上有很多會話保留了library cache lock，根據(jù)我前面的描述，能夠知道這個(gè)鎖模式是NULL的模式，但是由于過程沒在運(yùn)行，library cache pin沒加鎖，pins等于0.
下面看下如何找到哪個(gè)/些會話在執(zhí)行過程？既然在執(zhí)行就代表這個(gè)過程的游標(biāo)是打開的，我們可以看看v$open_cursor這個(gè)視圖。
在寫這篇博文之前，我沒有意識到查找哪些會話在正在執(zhí)行某個(gè)過程會是這么的艱難。
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è)過程。locks為4代表有4個(gè)會話持有了這個(gè)過程上null模式的library cache lock，還能推測出其中有3個(gè)locks是
這些會話歷史執(zhí)行過這個(gè)過程，當(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è)過程的sid，但是不難發(fā)現(xiàn)我們查詢的結(jié)果顯示的是4條記錄，也就是說這個(gè)視圖會把當(dāng)前游標(biāo)處于
open狀態(tài)的都顯示出來，這里面只有一個(gè)會話是正在執(zhí)行我們關(guān)注的過程。這個(gè)時(shí)候我們可以借助v$session來查看這些會話當(dāng)前在執(zhí)行哪些sql來判定
如果執(zhí)行的sql包含在我們關(guān)注的過程里，那么就能定位到執(zhí)行我們關(guān)注的過程的會話。
@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)程號是1606066，當(dāng)前正運(yùn)行anfr2phncqn6t（sql_id)這個(gè)語句，最終定位到這個(gè)sql是我們存儲過程里的。而其他3個(gè)會話處于空閑狀態(tài)。
看來在oracle里定位某個(gè)過程正在被哪些會話所執(zhí)行并不是一件容易的事。其實(shí)整個(gè)過程熟悉后，定位起來也不麻煩。
附帶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)行的存儲過程”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學(xué)到更多知識，如果覺得文章不錯(cuò)，請把它分享出去讓更多的人看到。
重慶分公司
重慶分公司
