Friday, July 15, 2011

CJQ WAITING FOR 'ENQ: JS - WDW OP'

I found something blocking by CJQ (waiting) on RAC and the jobs(DBMS_SCHEDULER) were hanging on all nodes.

I use Oracle 10.2.0.3 RAC, then found out on My Oracle Support DBMS_SCHELUER NOT RUNNING THE JOBS, CJQ WAITING FOR 'ENQ: JS - WDW OP' [ID 564957.1]
SQL> select * from gv$lock where block <> 2;

INST_ID ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
3 000000021F9492F0 000000021F949310 872 JS 0 8 0 6 2 0
2 000000021F9495E8 000000021F949608 872 JS 0 8 0 6 1 0
2 000000021F949550 000000021F949570 872 JS 0 8 6 0 10641 1
1 000000021F9492F0 000000021F949310 872 JS 0 8 0 6 0 0
4 000000021F9492F0 000000021F949310 872 JS 0 8 0 6 4 0

SQL> select * from gv$session_wait_history where inst_id = 2 and sid =872;

INST_ID SID SEQ# EVENT# EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_TIME WAIT_COUNT
---------- ---------- ---------- ---------- -------------------- --------------- ---------- ------------ ---------- -------------------- ---------- ---------- ----------
2 872 1 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 39 1
2 872 2 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 49 1
2 872 3 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 49 1
2 872 4 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 49 1
2 872 5 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 49 1
2 872 6 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 49 1
2 872 7 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 49 1
2 872 8 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 49 1
2 872 9 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 49 1
2 872 10 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 49 1
Found... 'ENQ: JS - WDW OP' enqueue. Oracle recommend to kill CJQ process on OS.
$ ps -aef |grep -i CJQ0
oracle 26813 1 0 Mar02 ? 01:51:57 ora_cjq0_DB2

$ kill -9 26813

$ ps -aef |grep -i CJQ0
oracle 4160 1 0 01:00 ? 00:00:00 ora_cjq0_DB2
*** everything OK, system no problem***

Why we should kill it... "enq: JS - wdw op" enqueue!!! When CJQ WAITING FOR 'ENQ: JS - WDW OP' ...The jobs(DBMS_SCHEDULER) are hanging on all nodes.
After killed CJQ process on OS... everything will be fine again.

No comments: