客户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 oct=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 off=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 oct=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 off=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 off=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 oct=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 off=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 off=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 oct=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 off=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 off=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 oct=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 off=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 off=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代替数据泵。