檢視正在執行的儲存過程
[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
/
最近專案一直在對錶增加欄位，很多失效物件需要編譯，經常發現由於過程正在執行因此導致編譯的會話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
/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28211342/viewspace-2132809/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL--儲存過程與檢視MySql儲存過程
- mysql檢視儲存過程show procedure status;MySql儲存過程
- 檢視、儲存過程以及許可權控制練習儲存過程
- 檢視正在執行的 Linux 系統版本Linux
- 瞭解使用mysql 的檢視、儲存過程、觸發器、函式....MySql儲存過程觸發器函式
- 如何查詢一個儲存過程是否在執行儲存過程
- 應用儲存過程執行報錯解決方案儲存過程
- 如何把SQL Server中一個表，一個儲存過程，一個檢視等改為系統表，系統儲存過程，系統檢視等...SQLServer儲存過程
- oracle的儲存過程Oracle儲存過程
- 在Linux中，如何檢視所有正在執行的程序？Linux
- 如何在Linux中檢視所有正在執行的程式Linux
- 不為人知的技術--Oracle並行非同步執行儲存過程Oracle並行非同步儲存過程
- Oracle儲存過程乾貨（一）：儲存過程基礎Oracle儲存過程
- SQL 儲存過程裡呼叫另一個儲存過程SQL儲存過程
- 用於檢視配置的儲存過程 | 全方位認識 sys 系統庫儲存過程
- 儲存過程與儲存函式儲存過程儲存函式
- SQLSERVER儲存過程SQLServer儲存過程
- 呼叫儲存過程儲存過程
- mysql 儲存過程MySql儲存過程
- unidac儲存過程儲存過程
- firedac儲存過程儲存過程
- Oracle儲存過程Oracle儲存過程
- all許可權使用者無法執行儲存過程儲存過程
- 深入解讀MySQL InnoDB儲存引擎Update語句執行過程MySql儲存引擎
- Sqlserver中的儲存過程SQLServer儲存過程
- Mysql 儲存過程的使用MySql儲存過程
- MySQL 儲存過程進行切換表MySql儲存過程
- mssql 儲存過程呼叫另一個儲存過程中的結果的方法分享SQL儲存過程
- 檢視一個正在執行的sql的執行計劃(explain for connection processlist_id)SQLAI
- JdbcTemplate調儲存過程JDBC儲存過程
- 造數儲存過程儲存過程
- 儲存過程——遊標儲存過程
- 儲存過程 傳 datatable儲存過程
- JAVA儲存過程(轉)Java儲存過程
- MySQL之儲存過程MySql儲存過程
- MySQL---------儲存過程MySql儲存過程
- linux呼叫儲存過程Linux儲存過程
- Winform呼叫儲存過程ORM儲存過程
- mysql儲存過程整理MySql儲存過程