sykace

26 积分	0 好友	1 主题

发消息

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

1^#

发表于 2012-5-8 15:49:44 | 查看: 7888| 回复: 4

目前数据库的一个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

enqjd.zip

1.06 MB, 下载次数: 1114

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2012-5-8 22:09:06

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

3^#

发表于 2012-5-8 23:20:32

嗯，非常感谢ML的解答。

回复只看该作者道具举报

wzhihua

4^#

发表于 2012-5-9 10:02:23

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

5^#

发表于 2012-5-15 09:42:41

通过本帖，我一直在学习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解释或提供些相关资料。

非常感谢！！

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册