导航：首页 > 服务器 >

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

发表于：2024-11-15 作者：千家信息网编辑

千家信息网最后更新 2024年11月15日，这篇文章将为大家详细讲解有关sql如何查看正在运行的存储过程，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。最近项目一直在对表增加字段，很多失效对象需要编译，经常发现

千家信息网最后更新 2024年11月15日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
/