mysql查询正在执行的存储过程_查看正在运行的存储过程

[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

/