问题场景:
一个比较小的表分了近200个区,使用impdp导入不到100M的数据的时候,导入很快可以成功,但是状态始终停在'WORK WAITING',
查clert.log和impdp.log并无报错信息出现;查awr报表,显示row cache lock 严重,top sql里面有impdp相关的语句:
sql module:
Data Pump Master
sql text:
1,BEGIN SYS.KUPM$MCP.MAINLOOP(); END;
2,BEGIN SYS.KUPM$MCP.MAIN('SYS_IMPORT_FULL_01', 'DATAMIG', 'KUPC$C_1_20120616032555', 'KUPC$S_1_20120616032555', 0, 0); END;
查相关session:
select s.program, s.sid, s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d where p.addr=s.paddr and s.saddr=d.saddr;
PROGRAM SID STATUS USERNAME JOB_NAME SPID SERIAL# PID
udi@crm1 (TNS V1-V3) 1920 ACTIVE DATAMIG SYS_IMPORT_FULL_01 1446304 59968 150
oracle@crm1 (DM00) 1637 ACTIVE DATAMIG SYS_IMPORT_FULL_01 1572952 39700 198
udi@crm1 (TNS V1-V3) 1499 ACTIVE DATAMIG SYS_IMPORT_FULL_01 1208836 45668 422
oracle@crm1 (DW01) 1693 ACTIVE DATAMIG SYS_IMPORT_FULL_01 1368160 60116 428
udi@crm1 (TNS V1-V3) 2104 INACTIVE DATAMIG SYS_IMPORT_FULL_01 1495352 44692 476
相关等待:
select * from v$session_wait a where a.SID in(select SID from v$session where MODULE like 'Data Pump%');
SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE WAIT_TIME_MICRO TIME_REMAINING_MICRO TIME_SINCE_LAST_WAIT_MICRO
1637 5621 row cache lock cache id 11 000000000000000B mode 0 00 request 5 0000000000000005 3875070507 4 Concurrency 0 0 WAITING 27371 2972629 0
1693 18062 wait for unread message on broadcast channel channel context 5.04403E+17 070000016131A378 channel handle 5.04403E+17 070000016952FB08 0 00 2723168908 6 Idle 0 0 WAITING 495834 504166 0
等待事件:
row cache lock
wait for unread message on broadcast channel
怀疑是bug,但是metalink找了很多,并没有发现与这个描述相关的bug
后来trace了一下,发现
crm1>select sid,event,program from v$session where sid=2028;
SID EVENT PROGRAM
---------- ---------------------- -------------------
2028 row cache lock oracle@crm1 (DM00)
crm1>select p1text,p1,p2text,p2,p3text,p3 from v$session where event='row cache lock';
P1TEXT P1 P2TEXT P2 P3TEXT P3
------------- --- ------------- --- ------------- ---
cache id 11 mode 0 request 5
crm1>select parameter,count,gets,getmisses,modifications from v$rowcache where cache#=11;
no rows selected
From Trace file
*** 2012-06-17 03:28:46.571
WAIT #16: nam='row cache lock' ela= 2929709 cache id=11 mode=0 request=5 obj#=5650 tim=52602436069873
crm1>select * from dba_blockers;
HOLDING_SESSION
---------------
2028
应该是个bug,某个系统任务使得master DM00阻塞了其他DM0X,使得其他的状态变成了work waiting.
不过在metalink上并没有找到相关的描述。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16179598/viewspace-733230/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16179598/viewspace-733230/