共計(jì) 4467 個(gè)字符，預(yù)計(jì)需要花費(fèi) 12 分鐘才能閱讀完成。

自動(dòng)寫代碼機(jī)器人，免費(fèi)開通

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

最近項(xiàng)目一直在對(duì)表增加字段，很多失效對(duì)象需要編譯，經(jīng)常發(fā)現(xiàn)由于過程正在運(yùn)行因此導(dǎo)致編譯的會(huì)話 HANG 在那，直到過程運(yùn)行結(jié)束。
如果能有一個(gè)手段告訴我數(shù)據(jù)庫里有哪些過程正在運(yùn)行就好了，那么我們就可以選擇對(duì)這些過程依賴的表后加字段，避開這個(gè)問題。
可以通過以下查詢來定位到正在運(yùn)行的存儲(chǔ)過程：
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ì)話持有的，也不能告訴你持有的模式。如果僅僅是為了能夠順利編譯通過過程，知道上述信息也就夠了。
進(jìn)一步的，如果你想了解到有哪些會(huì)話正在執(zhí)行這個(gè)過程，那么還得費(fèi)點(diǎn)勁才性。
其實(shí)上面查詢語句的條件 locks 大于 0 不是必須的：
1）存儲(chǔ)過程的運(yùn)行過程中，library cache lock 會(huì)加一個(gè) null 的鎖，library cache pin 會(huì)加一個(gè) s 的鎖。
靠這個(gè)鎖來保護(hù)存儲(chǔ)過程運(yùn)行中代碼存儲(chǔ)的內(nèi)存 HEAP 不會(huì)被刷出去。如果在存儲(chǔ)過程運(yùn)行運(yùn)行過程中，你去編譯那么就會(huì)遭遇 library cache pin 等待
因?yàn)榫幾g的會(huì)話需要獲取 x 模式的 library cache pin，這個(gè) x 模式與執(zhí)行這個(gè)過程的會(huì)話持有的 s 模式不兼容而發(fā)生等待。
2）但是歷史上一個(gè)會(huì)話如果執(zhí)行某個(gè)過程的次數(shù)大于 3 次，那么這個(gè)會(huì)話也可能保留對(duì)這個(gè) library cache 對(duì)象的 null 模式的 library cache lock，
即使這個(gè)會(huì)話當(dāng)前沒有執(zhí)行這個(gè)過程也會(huì)保留這個(gè) null 的 library cache lock. 對(duì) library cache pin 不加任何鎖，這個(gè)功能是開啟 session_cached_cursors 后的作用。
這個(gè)參數(shù)的作用當(dāng)然不僅僅限制與 PL/SQL 過程，對(duì)游標(biāo)依然如此、保留這個(gè) null 的 library cache lock 的作用是，pga 里保留了指向 library cache 對(duì)象的指針，下次解析
可以精確定位，不用在長時(shí)間（相對(duì)的）的持有 library cache latch 的情況下去 hash bucket 里去搜索了。
根據(jù)上面的論述我們可以知道，過程在執(zhí)行的話，pin 一定要持有，過程不執(zhí)行 pin 一定不持有（編譯持有時(shí)間極端，我們可以不考慮），那么 pins 0 就可以代表了這個(gè)
過程有 n 個(gè)會(huì)話在運(yùn)行它了，n 的值等于 pins 的值。

select name,locks,pins
   from v$db_object_cache
 where type= PROCEDURE and rownum

NAME                             LOCKS      PINS
—————————————- ———- ———-
P_GLOBAL_ACCOUNT_SM                      0        0
BUILD_BUFFER                          11        0
BUILD_BUFFER                          11        0
可以看到過程上有很多會(huì)話保留了 library cache lock，根據(jù)我前面的描述，能夠知道這個(gè)鎖模式是 NULL 的模式，但是由于過程沒在運(yùn)行，library cache pin 沒加鎖，pins 等于 0.
下面看下如何找到哪個(gè) / 些會(huì)話在執(zhí)行過程？既然在執(zhí)行就代表這個(gè)過程的游標(biāo)是打開的，我們可以看看 v$open_cursor 這個(gè)視圖。
在寫這篇博文之前，我沒有意識(shí)到查找哪些會(huì)話在正在執(zhí)行某個(gè)過程會(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è)過程。locks 為 4 代表有 4 個(gè)會(huì)話持有了這個(gè)過程上 null 模式的 library cache lock，還能推測(cè)出其中有 3 個(gè) locks 是
這些會(huì)話歷史執(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è)視圖會(huì)把當(dāng)前游標(biāo)處于
open 狀態(tài)的都顯示出來，這里面只有一個(gè)會(huì)話是正在執(zhí)行我們關(guān)注的過程。這個(gè)時(shí)候我們可以借助 v$session 來查看這些會(huì)話當(dāng)前在執(zhí)行哪些 sql 來判定
如果執(zhí)行的 sql 包含在我們關(guān)注的過程里，那么就能定位到執(zhí)行我們關(guān)注的過程的會(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è)語句，最終定位到這個(gè) sql 是我們存儲(chǔ)過程里的。而其他 3 個(gè)會(huì)話處于空閑狀態(tài)。
看來在 oracle 里定位某個(gè)過程正在被哪些會(huì)話所執(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)行的存儲(chǔ)過程”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。

向 AI 問一下細(xì)節(jié)

丸趣 TV 網(wǎng) – 提供最優(yōu)質(zhì)的資源集合！