13518219792
這篇文章將為大家詳細講解有關sql如何查看正在運行的存儲過程，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。
創新互聯于2013年創立，是專業互聯網技術服務公司，擁有項目網站建設、網站設計網站策劃，項目實施與項目整合能力。我們以讓每一個夢想脫穎而出為使命，1280元上街做網站,已為上家服務,為上街各地企業和個人服務,聯系電話:13518219792
最近項目一直在對表增加字段，很多失效對象需要編譯，經常發現由于過程正在運行因此導致編譯的會話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 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有非空閑等待，它的操作系統進程號是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
/
關于“sql如何查看正在運行的存儲過程”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。
