sqlldr效能調優測試

接著續上次提到的sqlldr的效能問題，載入一個表資料400多萬條記錄，竟然用了6個多小時。最後大家爭論不休的時候，我發現應該是網路的問題。
http://blog.itpub.net/23718752/viewspace-1182534/
今天客戶IT的同事把網路做了調整，他們就想看看到底改進有多大。
下面是測試的一些記錄。
 

àoriginal logs for issue table, loading around 6 hours.

Total logical records skipped:          0

Total logical records read:       4096786

Total logical records rejected:         0

Total logical records discarded:        0

Run began on Wed Jun 11 08:52:55 2014

Run ended on Wed Jun 11 14:57:40 2014

Elapsed time was:     06:04:44.05

CPU time was:         00:00:38.18

àafter network enhanced, loading time has reduced to around 4 mins.

Total logical records skipped:          0

Total logical records read:       4096786

Total logical records rejected:         0

Total logical records discarded:        0

Run began on Mon Jun 16 12:58:01 2014

Run ended on Mon Jun 16 13:02:15 2014

Elapsed time was:     00:04:13.28

CPU time was:         00:00:30.00

àconsidering impact from index, I loaded data again, keep table structure,index exactly the same.

Total logical records skipped:          0

Total logical records read:       4096786

Total logical records rejected:         0

Total logical records discarded:        0

Run began on Mon Jun 16 13:09:34 2014

Run ended on Mon Jun 16 13:16:37 2014

Elapsed time was:     00:07:02.61

CPU time was:         00:00:31.23

àtune sqlldr command with below options, keep table structure,index exactly the same.

parallel=true bindsize=7500000 readsize=7500000 streamsize=7500000 rows=50000

Total logical records skipped:          0

Total logical records read:       4096786

Total logical records rejected:         0

Total logical records discarded:        0

Run began on Mon Jun 16 13:21:13 2014

Run ended on Mon Jun 16 13:24:19 2014

Elapsed time was:     00:03:05.75

CPU time was:         00:00:41.27



所以綜上所述，在排查了cpu,io,快取，網路的因素後，還可以使用一些選項來最佳化sqlldr,在某種程度上得到提升。