首页 > Linux操作系统 > Linux操作系统 > 数据泵导入分区表长时间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代替数据泵。
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/4227/viewspace-746401/ ,如需转载,请注明出处,否则将追究法律责任。