sql如何查看正在运行的存储过程

这篇文章将为大家详细讲解有关sql如何查看正在运行的存储过程，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

最近项目一直在对表增加字段，很多失效对象需要编译，经常发现由于过程正在运行因此导致编译的会话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如何查看正在运行的存储过程”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。