# 44559
sunyunyi
|
|
To: 盖老师
我想问一下关于oracle enq js - wdw op 等待事件产生的原因?
From: sunyunyi 2010.03.14 23:17
|
|
|
|
|
|
|
|
|
|
To: sunyunyi
从这里可以找到JS的含义
http://www.eygle.com/archives/2009/11/v_dollar_enqueue_statis.html
oracle enq js - wdw op如果是长时间的等待,可能是JOB的运行出了问题,可以尝试重启或kill CJQ0进程,如果频繁出现,需要考虑确认是否是Bug。
Metalink 564957.1 记录了这样一种情况:
Symptoms
Everything is scheduled via DBMS_SCHEDULER.
The problem is that all jobs are locked and cannot run.
It is a 2 node RAC.
The jobs are hanging on both nodes becasue nothing is working for the DBMS_SCHEDULER at the window-times.
Cause
select * from gv$lock where block <> 2;
INST_ID ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
1 000000011F7E6200 000000011F7E6220 322 JS 0 8 0 6 3 0
2 000000011F7E0D70 000000011F7E0D90 321 JS 0 8 0 6 0 0
2 000000011F7E0B10 000000011F7E0B30 321 JS 0 8 6 0 582858 1
select * from gv$session_wait_history where inst_id = 2 and sid = 321;
INST_ID SID SEQ# EVENT# EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_TIME WAIT_COUNT
2 321 1 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 49 1
2 321 2 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 49 1
2 321 3 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 49 1
2 321 4 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 49 1
2 321 5 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 49 1
2 321 6 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 49 1
2 321 7 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 49 1
2 321 8 216 library cache lock handle address 4805070384 lock address 4063778832
100*mode+namespace 351 0 1
2 321 9 3 rdbms ipc message timeout 200 0 0 195 1
2 321 10 875 enq: JS - wdw op name|mode 1246953478 service ID 0 queue type 8 39 1
Found 'ENQ: JS - WDW OP' enqueue.
Solution
From sysstem state dump, PROCESS 15 CJQ0 is waiting for 'enq: JS - wdw op'
for X mode, but it is also holding it in X mode:=> Self deadlock
PROCESS 15:
----------------------------------------
SO: 0x11f47e310, type: 2, owner: (nil), flag: INIT/-/-/0x00
(process) Oracle pid=15, calls cur/top: 0x11f6f25d0/0x11f6f25d0, flag:
(2) SYSTEM
oracle@fons.uza.uia.ac.be (CJQ0)
...
...
SO: 0x11f6b3498, type: 4, owner: 0x11f47e310, flag: INIT/-/-/0x00
(session) sid: 321 trans: 0x118c33398, creator: 0x11f47e310, flag:
(100051) USR/- BSY/-/-/-/-/-
DID: 0002-000F-00000003, short-term DID: 0002-000F-00000004
txn branch: (nil)
oct: 0, prv: 0, sql: (nil), psql: (nil), user: 0/SYS
waiting for 'enq: JS - wdw op' blocking sess=0x(nil) seq=22514
wait_time=0 seconds since wait
started=3
name|mode=4a530006, service ID=0, queue type=8
Dumping Session Wait History
for 'enq: JS - wdw op' count=1 wait_time=489326
name|mode=4a530006, service ID=0, queue type=8
....
SO: 0x11f7e0d70, type: 5, owner: 0x11f6b3498, flag: INIT/-/-/0x00
(enqueue) JS-00000000-00000008 DID: 0002-000F-00000003
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 res_flag: 0x3
res: 0x0x11f8a44d0, req: X, lock_flag: 0x8
own: 0x0x11f6b3498, sess: 0x0x11f6b3498, proc: 0x0x11f47e310, prv:
0x0x11f8a44f0
slk: 0x0x11d1b6028
....
SO: 0x11f7e0b10, type: 5, owner: 0x11f6b3498, flag: INIT/-/-/0x00
(enqueue) JS-00000000-00000008 DID: 0002-000F-00000003
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 res_flag: 0x3
res: 0x0x11f8a44d0, mode: X, lock_flag: 0x0
own: 0x0x11f6b3498, sess: 0x0x11f6b3498, proc: 0x0x11f47e310, prv:
0x0x11f8a44e0
slk: 0x0x11d1b5ed8
Solution
=======
To kill the CJQ0 process from OS.
供参考!
From: eygle 2010.03.15 01:07
|
|
|
|
|
|
|
# 44551
ashaji
|
|
To:
盖老师:
您好,关于一个问题目前正在困扰着我,希望能够得到您的帮助。
我看yangtingkun的博客中说,分区表使用ENABLE ROW MOVEMENT是有副作用的。即表无法使用LOGMINER中的SQL_UNDO来进行恢复
http://yangtingkun.itpub.net/post/468/11764
由于stream是基于LOGMINER进行的,那么是否意味着对于上述情况,我做stream是无用的
From: ashaji 2010.02.25 05:57
|
|
|
|
|