- 论坛徽章:
- 0
|
换了一台新的SUN服务器，内存为16G，但是imp的速度却是老机器的近3倍（老机器只有4G内存）
除了imp，数据库没有任何其他操作
查看了一下imp时的IO情况，iostat -zx 2
device r/s w/s kr/s kw/s wait actv svc_t %w %b
md2 1.0 139.6 8.0 1116.6 0.0 1.3 9.3 0 87
md4 6.0 0.0 768.4 0.0 0.0 0.0 2.7 0 2
md21 0.5 140.1 4.0 1120.6 0.0 1.0 7.0 0 80
md22 0.5 139.6 4.0 1116.6 0.0 1.1 7.5 0 83
md41 3.0 0.0 384.2 0.0 0.0 0.0 1.9 0 1
md42 3.0 0.0 384.2 0.0 0.0 0.0 3.4 0 1
sd0 1.0 81.0 8.0 648.3 0.0 0.5 6.2 0 50
sd1 0.5 140.1 4.0 1120.6 0.0 1.0 6.9 0 80
sd4 3.0 0.0 384.2 0.0 0.0 0.0 1.9 0 1
sd5 0.5 140.1 4.0 1120.6 0.0 1.1 7.5 0 83
sd7 3.0 0.0 384.2 0.0 0.0 0.0 3.4 0 1
md2的速度只有1M/s左右，但是busy已经相当高了，但是直接在OSmkfile一个文件，速度可达50M/s左右，甚至在oracle中create tablespace时也有这个速度，因为redo log放在md2上，查看了alert.log，redo switch的速度很快（2分钟切一次，没有开启archive），将redo log size从100M 增加到800M，并从3组增加到5组，log buffer已加至4M
imp时buffer设置如下：
buffer=1024000000 RECORDLENGTH=65535 commit=y
我已经没招了，到底是什么导致它的imp如此之慢，只能上来求助大侠们了
以下是imp期间出的一个perfstat report，间隔大致为15分钟
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
TBGEPROD 1505954713 tbgeprod 1 9.2.0.7.0 NO SHGUPOR5
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 2 01-Feb-09 11:04:29 14 3.9
End Snap: 3 01-Feb-09 11:28:05 14 4.8
Elapsed: 23.60 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 5,120M Std Block Size: 8K
Shared Pool Size: 1,504M Log Buffer: 4,096K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 707,565.04 1,629,125.35
Logical reads: 884.89 2,037.40
Block changes: 608.47 1,400.97
Physical reads: 0.06 0.15
Physical writes: 88.49 203.74
User calls: 1.04 2.40
Parses: 0.44 1.02
Hard parses: 0.07 0.17
Sorts: 0.30 0.68
Logons: 0.00 0.00
Executes: 1.40 3.22
Transactions: 0.43
% Blocks changed per Read: 68.76 Recursive Call %: 87.92
Rollback per transaction %: 0.00 Rows per Sort: 77.36
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 94.53 Soft Parse %: 83.39
Execute to Parse %: 68.37 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 108.33 % Non-Parse CPU: 99.90
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 12.82 13.02
% SQL with executions>1: 32.64 32.18
% Memory for SQL w/exec>1: 24.75 24.21
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file parallel write 2,271 2,125 47.63
log file parallel write 2,466 1,040 23.30
log file sync 1,374 911 20.41
CPU time 372 8.33
control file parallel write 489 11 .25
-------------------------------------------------------------
^LWait Events for DB: TBGEPROD Instance: tbgeprod Snaps: 2 -3
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write 2,271 0 2,125 936 3.7
log file parallel write 2,466 0 1,040 422 4.0
log file sync 1,374 759 911 663 2.2
control file parallel write 489 0 11 23 0.8
log file switch completion 5 0 2 420 0.0
db file sequential read 44 0 1 30 0.1
log file single write 8 0 0 9 0.0
direct path write 46 0 0 0 0.1
control file sequential read 353 0 0 0 0.6
direct path read 46 0 0 0 0.1
log file sequential read 8 0 0 0 0.0
SQL*Net more data to client 4 0 0 0 0.0
SQL*Net message from client 1,389 0 2,402 1729 2.3
SQL*Net more data from clien 414,039 0 11 0 673.2
SQL*Net message to client 1,389 0 0 0 2.3
-------------------------------------------------------------
^LBackground Wait Events for DB: TBGEPROD Instance: tbgeprod Snaps: 2 -3
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write 2,271 0 2,125 936 3.7
log file parallel write 2,466 0 1,040 422 4.0
control file parallel write 489 0 11 23 0.8
log file single write 8 0 0 9 0.0
db file sequential read 1 0 0 21 0.0
direct path write 46 0 0 0 0.1
control file sequential read 227 0 0 0 0.4
rdbms ipc reply 1 0 0 0 0.0
direct path read 46 0 0 0 0.1
log file sequential read 8 0 0 0 0.0
rdbms ipc message 3,658 2,260 6,487 1774 5.9
smon timer 6 5 1,623 ###### 0.0
pmon timer 471 471 1,380 2930 0.8
-------------------------------------------------------------
^LSQL ordered by Gets for DB: TBGEPROD Instance: tbgeprod Snaps: 2 -3
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
1,242,502 614 2,023.6 99.2 368.23 361.64 3927692028
Module: imp@SHGUPOR5 (TNS V1-V3)
INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "C_COMPONENTRECORD" ("C_
COMPONENTRECORDOID", "ACTUALPAIDVALUE", "AMENDINGVALUE", "AMENDV
ALUE", "COMPONENTID", "DESCRIPTION", "EMPLOYEEID", "PAIDVALUE",
"PROCESSID", "REMARK") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :
9, :10)
4,568 142 32.2 0.4 0.09 0.11 1254950678
select file# from file$ where ts#=:1
4,087 2 2,043.5 0.3 0.47 0.46 859273950
Module: TOAD 8.0.0.47
Select ROUND(MAX(d.bytes)/1024/1024,2) "total MB", DECOD
E(SUM(f.bytes), null, 0, ROUND(SUM(f.Bytes)/1024/1024,2)) "Free
MB" , d.file_name "Datafile name", DECODE( SUM(f.
Bytes), null, 0, ROUND((MAX(d.bytes)/1024/1024) - (SUM(f.bytes)/
1024/1024),2)) "Used MB", ROUND(MAX(d.bytes)/1024,2) "to
566 283 2.0 0.0 0.11 0.09 1316169839
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date < :2)) or ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
) and (this_date is null) order by next_date, job
534 89 6.0 0.0 0.08 0.06 3948238198
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts
=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13
, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh
int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1
and file#=:2 and block#=:3
445 89 5.0 0.0 0.08 0.05 1380021873
update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv
2=:7,priv3=:8 where ts#=:1 and user#=:2
283 283 1.0 0.0 0.01 0.03 1693927332
select count(*) from sys.job$ where (next_date > sysdate) and (n
ext_date < (sysdate+5/86400))
108 8 13.5 0.0 0.02 0.02 4080861370
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_
obj#, d_owner#, nvl(property,0),subname from dependency$,obj$ wh
ere d_obj#=:1 and p_obj#=obj#(+) order by order#
94 12 7.8 0.0 0.00 0.43 986338823
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece fr
om idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
具体内容见附件 |
|
免费注册
发表于 2009-02-01 11:58
