查看Oracle中存储过程长时间被卡住的原因

时间:2021-07-09 13:26:04

1:查V$DB_OBJECT_CACHE

SELECT * FROM V$DB_OBJECT_CACHE WHERE name='CUX_OE_ORDER_RPT_PKG' AND LOCKS!='0';

注意:CUX_OE_ORDER_RPT_PKG 为存储过程的名称。

发现 locks=2

2:按对象查出sid的值

select /*+ rule*/  SID from V$ACCESS WHERE object='CUX_OE_ORDER_RPT_PKG';

注意:CUX_OE_ORDER_RPT_PKG 为存储过程的名称。

3:查sid,serial#

SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='刚才查到的SID';

4、根据会话id(sid),此会话的等待事件:

  1. select * from v$session where sid=***;

event字段即为等待事件。查询后我们发现这个会话等待事件为SQL*Net message from dblink;在查看会话的logon_time为两天前。这个时间远超过我们估计时间。

5、根据会话id查看此会话正在执行的sql语句

  1. select sql_text from v$sqlarea where address= (select   sql_address  from v$session where sid=***);

查询后发现正在执行的sql语句为通过dblink到远程数据库上A表查询数据,插入到B表。

6、连接远程数据库,查询当前被锁的对象

  1. select * from v$locked_object lo ,
  2. all_objects  ao    where lo.OBJECT_ID= ao.object_id ;

查看后发现远程数据库中并没有涉及到A、B表被锁

7、查看远程数据的会话:

  1. select * from v$session where terminal like '%机器名%'  and program='Oracle.exe'

使用dblink连接远程数据库,在远程数据库上的会话的program应该是是oracle.exe

查询后发现,两个远程库有时候根本没有相关会话,有时候可能有相关会话,但其等待事件是 SQL*Net message from client 远程库在等待本地Oracle给他发请求。

本地库等dblink远程库,远程库等待client消息。看来这个存储过程是不可能执行完了。

具体什么原因造成了,还不清楚。

这里给出的处理方法就是杀死会话

http://blog.csdn.net/fupei/article/details/7325190

具体步骤可参考上面的文章

一些项目中使用了job定期执行sql语句。如果要执行的sql语句是基于dblink对远程数据库的访问,那么有时候就会出现该sql语句长时间执行一直不结束的情况。并且这时在远程数据库上并没有锁导致该sql语句等待(这可能是由于网络问题触发的oracle的一个bug吧,远程数据库与本地数据之间有防火墙时比较容易出现这个现象)。

下面总结了如何判断该job是否长时间执行没结束,并说明了处理步骤。

1)、观察job情况。
   system用户下执行语句select * from  dba_jobs;找到有问题的job,记录下该job在查询结果中job列的取值,该取值称为job号。
   broken字段为N,且this_date字段的时间比当前时间减去执行周期要晚(根据interval字段判断),则job是正常的。如果this_date字段没有值,一般认为job当前没有在执行。
   如果broken字段N,并且this_date时间不对(例如是几个小时以前,甚至几天以前),则说明该job某一次周期一直没有执行完。
   如果出现这种现象,就说明该job可能出问题了。
     
2)、查找该job目前正在执行时的会话编号sid
   select * from  dba_jobs_running where job='刚才查到的job号';
   在返回结果中记录sid列的取值

3)、查看该会话
   select * from v$session where sid='刚才查到的sid'
   记录下返回结果的 serial#列(会话序列号),paddr列(线程地址)

4)、 取得会话的线程号
select spid from  v$process  where   addr='刚才查到的线程地址' ;
记录下列spid,称为线程号

5)、使用oracle命令杀会话
     alter system kill session '会话编号sid,会话序列号serial#';

6)、查看是否成功杀掉该会话(方法与步骤一相同,多执行几次select * from  dba_jobs;观察结果)

7)、如果没有杀掉会话,就是用操作系统命令杀线程(或进程)
    这里给出windows下杀oracle会话占用的线程的方法  
    登录到数据库所在的操作系统中,打开windows命令行,键入命令:  orakill 数据库sid  刚才查到的线程号spid

例如 orakill orcl  12345

这里给出一个自动清理问题job的存储过程,由于是存储过程,只能使用alter system kill 来杀会话,有时候会话只被标记为killed,并不能真正结束,job也无法启动下一个周期。

CREATE OR REPLACE PROCEDURE SYS.PRO_KILL_JOB AS
  /*清理job567 568 569 长期执行不结束的情况*/
  /*30分钟超时*/
 CURSOR   MYCUR     IS
   select     ' ALTER SYSTEM KILL SESSION '''||s.sid ||','||  s.SERIAL#||''' immediate '   AS SQL_KILL   , J.JOB
      from dba_jobs_running j,v$session s  
      where   j.sid=s.sid and
       this_date <(sysdate-30/24/60) and
       s.sid is not null and s.serial# is not null
       and
       ( j.job= 567
         or j.job=568
         or j.job=569  ) ;
 
  V_SQL_KILL  VARCHAR2(500);
  V_JOB NUMBER ;
 
 
BEGIN
   OPEN MYCUR;
  LOOP
    FETCH MYCUR
      INTO V_SQL_KILL , V_JOB;
    EXIT WHEN MYCUR%NOTFOUND;
    dbms_output.put_line(v_sql_kill);
    execute immediate  v_sql_kill ;
    COMMUNICATION.SP_DB_LOG('PRO_KILL_JOB', 1, NULL, V_JOB||' IS KILLED');
    COMMIT;
  END LOOP;
  CLOSE MYCUR;
EXCEPTION
  WHEN OTHERS THEN
    COMMUNICATION.SP_DB_LOG('PRO_KILL_JOB', 1, SQLCODE, SQLERRM);
    COMMIT;
END PRO_KILL_JOB;