Script to found out resource comsuming of Import

imp一个大的dmp file , 慢的狠，这里小小的介绍一下怎么简单的发现瓶颈




实际上，就是一个简单的script

select event , round(time_waited/100) as wait
from v$session_event w , v$session s
where lower(s.program) like 'imp%'
and w.sid= s.sid
and time_waited > 100
union all
select name ,round(value / 100) as cpu
from v$sesstat st , v$session s , v$statname n
where lower(s.program) like 'imp%'
and st.sid = s.sid
and st.statistic# = n.statistic#
and n.name like 'CPU used by this session%'
order by 2 desc
/


CPU used by this session 185
SQL*Net message from client 44
SQL*Net more data from client 32
log buffer space 16
log file switch completion 1
log file sync 1

看起来这是一个正常的结果

185sec在耗CPU

44+32 sec 在等待imp进程读数据

18 redo log相关wait


根据具体的wait可以进行tuning,最常见的可能有

1.log file sync

可能是使用commit=Y,而buffer不够大

2.direct path read/write

造index时sort_area_size/pga_aggraget_target不够大，可以考虑加大sort_area_size / pga_aggraget_target

3.log buffer wait

log buffer不够大

4.db file scatter read / buffer busy wait

db_cache_size不够大

5. log file switch completeion

多加几组redo log,或者加大redo log的大小


总而言之，随机应变……

对了，这个script只能在imp 完成之前run哦……