single-task message等待事件

1.问题说明

业务报告某套系统的1个JOB执行很慢,基本算挂起了。登录到数据库上,检查如下:

select s.sid, s.USERNAME, s.event, s.SQL_ID, s.LAST_CALL_ET, j.JOB, j.WHAT, j.LAST_DATE, j.NEXT_DATE 
from v$session s, dba_jobs_running r, dba_jobs j
where s.SID = r.SID
 and r.JOB = j.JOB
 and j.job = 1497;

通过以上脚本查到1497号JOB的的情况如下

       SID USERNAME        EVENT                                    SQL_ID             Number                     P1               P2           P3 LAST_CALL_ET
---------- --------------- ---------------------------------------- --------------- --------- ---------------------- ---------------- ------------ ------------
      6479 xxx             cursor: pin S wait on X                  5fx0sjxgf51rv                         1591903995   34394098106368  21498280415         3156

这个会话的等待事件是cursor: pin S wait on X,LAST_CALL_ET表明挂起已经近1小时。

2.查找阻塞者

cursor: pin S wait on X是mutex等待事件,查找它的阻塞会话方法如下:

select w.SID "Waiter",
       to_number(substr(to_char(rawtohex(p2raw)),
                        1,
                        case
                          when (select instr(platform_name, '64') from DBA_REGISTRY_DATABASE) > 0 then
                           8
                          else
                           4
                        end),
                 'XXXXXXXX') "Blocker"
  from v$session_wait w
 where w.EVENT = 'cursor: pin S wait on X';

    Waiter    Blocker
---------- ----------
      6479       8008

这里查到阻塞会话是8008,检查8008会话情况如下:

       SID USERNAME   PROGRAM                   MACHINE              SQL_ID        SQL_CHILD_NUMBER EVENT                          STATUS             P1 LAST_CALL_ET
---------- ---------- ------------------------- -------------------- ------------- ---------------- ------------------------------ ---------- ---------- ------------
      8008 xxx        oracle@xxxxx01 (J004)     xxxxx01              5fx0sjxgf51rv                1 single-task message            ACTIVE              0       429277

这个会话的等待事件是single-task message,将这个JOB杀掉后,被阻塞1497号JOB就可以正常跑下去了。
判断能不能杀,LAST_CALL_ET为429277,是个很长的时间了,另外也可以从logon_time来判断,我也看了这个阻塞会话的登录时间,正好是某个配置库出问题的时间,single-task message是dblink出问题的等待事件。

3.single-task message说明

single-task message 这个等待事件,oracle的解释是:
When running single task, this event indicates that the session waits for the client side of the executable.

相关问题:
Bug 7757687 : CURSOR: PIN S WAIT ON X BLOCKER “SINGLE-TASK MESSAGE”
Bug 16733457 : DB HANG – ‘LIBRARY CACHE PIN’ WAITING ON A ‘SINGLE-TASK MESSAGE’ EVENT
Bug 12871012 : J00X PROCESS HANGS AND WAITING FOR ‘SINGLE-TASK MESSAGE’

关于紫砂壶

感悟技术人生
此条目发表在OWI分类目录,贴了标签。将固定链接加入收藏夹。