ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据泵导入分区表长时间HANG住

数据泵导入分区表长时间HANG住

原创 Linux操作系统 作者:yangtingkun 时间:2012-09-07 23:23:53 0 删除 编辑

客户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/ ,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1964
  • 访问量
    11085795