JOB无法执行，CJQ0等待事件enq:JD

sykace

目前数据库的一个JOB一直处于等待状态，session等待事件为enq:JD.

SQL> select * from v$version;

BANNER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
NLSRTL Version 10.2.0.5.0 - Production

Maclean Liu(刘相兵

ODM DATA:

10.2.0.5  AIX

Open chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/161/34555/0xc822c590/8127396/SQL*Net message from client>
-- <0/119/3736/0xcb230408/14942880/enq: JD - contention>


cjq0   => sid 119   wait for enq: JD - contention     ospid 14942880

blocker  =>  sid 161  idel  ospid 8127396


cjq0  errorstack 4

cjq0 的stack call 是 kslwait=》kskthbwt＝》kslwaitns_timed=》ksliwat=》skgpwwait=》sskgpwwait

blocker 的信息

PROCESS 26:
  ----------------------------------------
  SO: 7000000c822c590, type: 2, owner: 0, flag: INIT/-/-/0x00
  (process) Oracle pid=26, calls cur/top: 0/7000000a9382568, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 110 0 4
              last post received-location: kslpsr
              last process to post me: 7000000cb22f428 1 6
              last post sent: 0 0 24
              last post sent-location: ksasnd
              last process posted by me: 7000000cb22ec38 1 6
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 7000000cb25fe78
    O/S info: user: oracle, term: UNKNOWN, ospid: 8127396


    SO: 7000000cb2d3880, type: 4, owner: 7000000c822c590, flag: INIT/-/-/0x00
    (session) sid: 161 trans: 0, creator: 7000000c822c590, flag: (100041) USR/- -/-/-/-/-/-
              DID: 0001-001A-03004F98, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 0, prv: 0, sql: 0, psql: 7000000c9bc7e08, user: 58/APPUSER
    service name: ECSAPP
    O/S info: user: Bob, term: BOB-666, ospid: 8056:3864, machine: WORKGROUP\BOB-666
              program: plsqldev.exe
    application name: PL/SQL Developer, hash value=1190136663
    action name: SQL Window - New, hash value=3399691616


blocker 是一个PL/SQL Developer 连接  ， 来源于 WORKGROUP\BOB-666   用户名是 58/APPUSER

    action name: Create / Modify object, hash value=555310690
==》这个PL/SQL Developer 连接 　正在创建或修改某个对象



enq: JD - contention



CJQ0 以 X mode request JD-00000000-00000000 ，但是sid=161的 PL/SQL developer连接已经以S mode hold JD resource

JD         Synchronizes dates between job queue coordinator and slave processes



      SO: 7000000cb37f020, type: 5, owner: 7000000cb2b75a8, flag: INIT/-/-/0x00
      (enqueue) JD-00000000-00000000    DID: 0001-000B-00000006
      lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x2
      req: X, lock_flag: 0x0, lock: 0x7000000cb37f040, res: 0x7000000c83a16a0
      own: 0x7000000cb2b75a8, sess: 0x7000000cb2b75a8, proc: 0x7000000cb230408, prv: 0x7000000c83a16c0

         SO: 7000000cb37fea8, type: 5, owner: 7000000cb2d3880, flag: INIT/-/-/0x00
      (enqueue) JD-00000000-00000000    DID: 0001-001A-03004F4F
      lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x2
      mode: S, lock_flag: 0x0, lock: 0x7000000cb37fec8, res: 0x7000000c83a16a0
      own: 0x7000000cb2d3880, sess: 0x7000000cb2d3880, proc: 0x7000000c822c590, prv: 0x7000000c83a16b0

      name=SYS.DBMS_IJOB

      name=UPDATE SYS.JOB$ SET NEXT_DATE = NVL( :B2 , TO_DATE('4000-01-01','YYYY-MM-DD')) WHERE JOB = :B1
      hash=e950b92a8840f62043e7b3999dc63cb2 timestamp=05-04-2012 16:09:20




      name=UPDATE SYS.JOB$ J SET J.INTERVAL# = NVL(:B3 ,'null') WHERE JOB = :B2 AND (:B1 = 'SYS' OR (J.POWNER != 'SYS' AND J.COWNER != 'SYS' AND J.LOWNER != 'SYS') OR SYS_CONTEXT('USERENV', 'SESSION_USER') = 'SYS')
      hash=3441114841b5dbfd06001f44cb625f05 timestamp=05-04-2012 16:09:20



可能是 用户在使用 PL/SQL developer 修改 某个JOB 的时间后没有commit 导致 相关的JD enqueue一直被hold ， 且没有 退出程序 。 导致CJQ0 hang住。



建议：

1. 退出 sid 161 的PL/SQL developer连接
2.  KILL sid=161 的session

sykace

嗯，非常感谢ML的解答。

wzhihua

mac老大有时间给讲讲怎么分析trace文件吗？需要什么工具还是怎么分析？
比如：怎么找到“blocker  =>  sid 161  idel  ospid 8127396”这个信息的？
通过等待事件可以找到

SO: 7000000cb2b75a8, type: 4, owner: 7000000cb230408, flag: INIT/-/-/0x00
    (session) sid: 119 trans: 0, creator: 7000000cb230408, flag: (51) USR/- BSY/-/-/-/-/-
              DID: 0001-000B-00000006, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 0, prv: 0, sql: 0, psql: 0, user: 0/SYS
    service name: SYS$BACKGROUND
    waiting for 'enq: JD - contention' wait_time=0, seconds since wait started=27
                name|mode=4a440006, 0=0, 0=0
                blocking sess=0x7000000cb2d3880 seq=2
那怎么找到blocker？？

wzhihua

通过本帖，我一直在学习trace文件的分析方法。通过这几天的学习，取得了一些进展，现在还有几个问题请maclean给解释下。
1、通过ass工具，可以得到如下结果：

System State 1
~~~~~~~~~~~~~~~~
1:
2:  waiting for 'pmon timer'
3:  waiting for 'rdbms ipc message'
4:  waiting for 'rdbms ipc message'
5:  waiting for 'rdbms ipc message'
6:  waiting for 'rdbms ipc message'
7:  waiting for 'rdbms ipc message'
8:  waiting for 'rdbms ipc message'
9:  waiting for 'smon timer'
10: waiting for 'rdbms ipc message'
11: waiting for 'enq: JD - contention' [Enqueue JD-00000000-00000000]
12: waiting for 'rdbms ipc message'
13: waiting for 'rdbms ipc message'
14:
15:
16: for 'Streams AQ: waiting for time management or cleanup tasks'
17: waiting for 'rdbms ipc message'
18: waiting for 'rdbms ipc message'
19: waiting for 'SQL*Net message from client'
20: waiting for 'Streams AQ: qmn coordinator idle wait'
21: waiting for 'SQL*Net message from client'
22: waiting for 'SQL*Net message from client'
     Cmd: Select
23: waiting for 'SQL*Net message from client'
24: waiting for 'SQL*Net message from client'
25: waiting for 'SQL*Net message from client'
26: waiting for 'SQL*Net message from client'
27: waiting for 'Streams AQ: qmn slave idle wait'
28: waiting for 'SQL*Net message from client'
29: waiting for 'SQL*Net message from client'
30: waiting for 'SQL*Net message from client'
31: waiting for 'SQL*Net message from client'
32: waiting for 'SQL*Net message from client'
33: waiting for 'SQL*Net message from client'
34: waiting for 'SQL*Net message from client'
35: waiting for 'SQL*Net message from client'
     Cmd: Select
36: waiting for 'SQL*Net message from client'
37: waiting for 'SQL*Net message from client'
38: waiting for 'SQL*Net message from client'
39: waiting for 'SQL*Net message from client'
40: waiting for 'SQL*Net message from client'
41: waiting for 'SQL*Net message from client'
42: waiting for 'SQL*Net message from client'
43: waiting for 'SQL*Net message from client'
44: waiting for 'SQL*Net message from client'
45: waiting for 'SQL*Net message from client'
46: waiting for 'SQL*Net message from client'
47: waiting for 'SQL*Net message from client'
48: waiting for 'SQL*Net message from client'
     Cmd: Select
50: waiting for 'SQL*Net message from client'
     Cmd: Select
51: waiting for 'SQL*Net message from client'
     Cmd: Select
52: waiting for 'SQL*Net message from client'
54: waiting for 'SQL*Net message from client'
55: waiting for 'SQL*Net message from client'
56: waiting for 'SQL*Net message from client'
57: waiting for 'SQL*Net message from client'
58: waiting for 'SQL*Net message from client'
59: waiting for 'SQL*Net message from client'
60: waiting for 'SQL*Net message from client'
61: waiting for 'SQL*Net message from client'
62: waiting for 'SQL*Net message from client'
63: last wait for 'SQL*Net message from client'
     Cmd: Select
64: last wait for 'SQL*Net message from client'
65: waiting for 'SQL*Net message from client'
66: waiting for 'SQL*Net message from client'
70: waiting for 'SQL*Net message from client'
73: waiting for 'SQL*Net message from client'[Latch waiting]
Blockers
~~~~~~~~

        Above is a list of all the processes. If they are waiting for a resource
        then it will be given in square brackets. Below is a summary of the
        waited upon resources, together with the holder of that resource.
        Notes:
        ~~~~~
         o A process id of '???' implies that the holder was not found in the
           systemstate.

                    Resource Holder State
Enqueue JD-00000000-00000000    26: waiting for 'SQL*Net message from client'
               Latch waiting    ??? Blocker

Object Names
~~~~~~~~~~~~
Enqueue JD-00000000-00000000
Latch waiting       <0/204/5/0xc8229de0/9765836/Streams AQ: waitin


42832 Lines Processed.

由上可知holder 26占有资源Enqueue JD-00000000-00000000 ，
而11: waiting for 'enq: JD - contention' [Enqueue JD-00000000-00000000]造成竞争。
问题：
1、holder26正在做什么怎样查找？即怎样找到2#楼中的：
name=UPDATE SYS.JOB$ SET NEXT_DATE = NVL( :B2 , TO_DATE('4000-01-01','YYYY-MM-DD')) WHERE JOB = :B1
      hash=e950b92a8840f62043e7b3999dc63cb2 timestamp=05-04-2012 16:09:20
2、每个process下有若干个so，这些so怎么理解？
因为没有账号不能到metlink查找相关资料，还请maclean解释或提供些相关资料。

非常感谢！！