資料泵匯入分割槽表長時間HANG住

客戶10.2.0.3環境資料庫在使用資料庫匯入分割槽表時，出現長時間HANG住的情況。

透過10046跟蹤，發現等待發生在空閒等待wait for unread message on broadcast channel上，部分10046 TRACE摘錄如下：

=====================
PARSING IN CURSOR #27 len=93 dep=2 uid=0 ct=3 lid=0 tim=1314162845109698 hv=3190910778 ad='5a938130'
SELECT NVL(SUM(data_io),0) FROM "SYS"."SEASHELL_ENTRY_P_20110126_88" WHERE process_order = :1
END OF STMT
PARSE #27:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162845109693
BINDS #27:
kkscoacd
Bind#0
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=206001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=2acd522c5888  bln=22  avl=03  flg=05
  value=-42
EXEC #27:c=0,e=121,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162845109900
FETCH #27:c=999,e=91,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=1,tim=1314162845110019
=====================
PARSING IN CURSOR #36 len=46 dep=2 uid=0 ct=47 lid=0 tim=1314162845110264 hv=420667605 ad='594460a0'
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
END OF STMT
PARSE #36:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162845110259
BINDS #36:
kkscoacd
Bind#0
  oacdty=121 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=206001 frm=00 csi=00 siz=4000 ff=0
  kxsbbbfp=2acd51ff8e30  bln=4000  avl=00  flg=15
Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=206001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=2acd522c5888  bln=22  avl=02  flg=05
  value=5
WAIT #36: nam='wait for unread message on broadcast channel' ela= 976833 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162846087749
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977485 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162847065320
*** 2012-08-23 14:19:16.395
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977438 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162848042834
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977564 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162849020507
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977425 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162849997982
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977515 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162850975546
EXEC #36:c=0,e=5865418,p=0,cr=4,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162850975764
ERROR #36:err=25228 tim=805377243
=====================
PARSING IN CURSOR #36 len=46 dep=2 uid=0 ct=47 lid=0 tim=1314162850975992 hv=420667605 ad='594460a0'
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
END OF STMT
PARSE #36:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162850975987
BINDS #36:
kkscoacd
Bind#0
  oacdty=121 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=206001 frm=00 csi=00 siz=4000 ff=0
  kxsbbbfp=2acd51ff8e30  bln=4000  avl=00  flg=15
Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=206001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=2acd522c5888  bln=22  avl=02  flg=05
  value=5
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977489 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162851954175
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977604 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162852931835
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977468 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162853909360
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977445 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162854886930
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977462 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162855864477
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977665 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162856842219
EXEC #36:c=1000,e=5866346,p=0,cr=4,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162856842423
ERROR #36:err=25228 tim=805377844
=====================
PARSING IN CURSOR #36 len=46 dep=2 uid=0 ct=47 lid=0 tim=1314162856842660 hv=420667605 ad='594460a0'
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
END OF STMT
PARSE #36:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162856842654
BINDS #36:
kkscoacd
Bind#0
  oacdty=121 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=206001 frm=00 csi=00 siz=4000 ff=0
  kxsbbbfp=2acd51ff8e30  bln=4000  avl=00  flg=15
Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=206001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=2acd522c5888  bln=22  avl=02  flg=05
  value=5
*** 2012-08-23 14:19:26.408
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977414 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162857820783
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977354 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162858798202
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977523 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162859775833
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977339 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162860753273
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977629 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162861730982
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977450 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162862708486
EXEC #36:c=1000,e=5865927,p=0,cr=4,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162862708674
ERROR #36:err=25228 tim=805378444
=====================
PARSING IN CURSOR #36 len=46 dep=2 uid=0 ct=47 lid=0 tim=1314162862708909 hv=420667605 ad='594460a0'
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
END OF STMT
PARSE #36:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162862708903
BINDS #36:
kkscoacd
Bind#0
  oacdty=121 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=206001 frm=00 csi=00 siz=4000 ff=0
  kxsbbbfp=2acd51ff8e30  bln=4000  avl=00  flg=15
Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=206001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=2acd522c5888  bln=22  avl=02  flg=05
  value=5
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977366 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162863686994
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977565 channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162864664610

顯然這種空閒等待的出現是不正常的，根據等待時間和等待的具體PL/SQL語句，可以確認為Bug 7439689 - impdp worker process may spin [ID 7439689.8]。這個bug影響的版本為10.2.0.4正是當前的版本，在10.2.0.5中解決了這個問題。此外在Solaris和HP-UX環境下，還有專門針對這個bug的補丁程式。

Oracle給出的臨時解決方案是用exp/imp代替資料泵。