比较direct方式使用并行和非并行选项的性能差异。

并行测试

创建测试表

create table sqlldr_paral

nologging

PARTITION BY HASH (sfzh)

(partition p1 tablespace users , partition p2tablespace zdry_tbs2 )

AS SELECT *FROM sfxx2 where 1=2 ;

sqlldr_paral.ctl

LOAD DATA

INFILE 'd:\flatfile\flatfile.dat'

APPEND

INTO TABLE sqlldr_paral

fields terminated by X'23'

TRAILING NULLCOLS

(zj,

…字段太多省略

cjdwdm)

sqlldr_paral.par

USERID=zdrygk/zdrygk

CONTROL='d:\flatfile\sqlldr_paral.ctl'

DATA='d:\flatfile\flatfile.dat'

LOG='d:\flatfile\sqlldr_paral.log'

DIRECT=true

parallel=true

ERRORS=10000

执行

D:\flatfile>sqlldrparfile=d:\flatfile\sqlldr_paral.par

SQL*Loader: Release 11.2.0.1.0 - Production onMon Jan 7 23:21:42 2013

Load completed - logical record count 3022375.

sqlldr_paral.log

Control File: d:\flatfile\sqlldr_paral.ctl

Data File: d:\flatfile\flatfile.dat

BadFile: d:\flatfile\flatfile.bad

DiscardFile: none specified

(Allowall discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 10000

Continuation: none specified

Path used: Direct- with parallel option. -----这里指定了并行选项

Table SQLLDR_PARAL, loaded from every logicalrecord.

Insert option in effect for this table: APPEND

TRAILING NULLCOLS option in effect

Table SQLLDR_PARAL:

3017264Rows successfully loaded.

5111Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauseswere failed.

0 Rowsnot loaded because all fields were null.

Dateconversion cache disabled due to overflow (default size: 1000)

Partition P1: 1509091 Rows loaded.

Partition P2: 1508173 Rows loaded.

Bind array size not used in direct path.

Column array rows : 5000

Stream buffer bytes: 256000

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 3022375

Total logical records rejected: 5111

Total logical records discarded: 0

Total stream buffers loaded by SQL*Loader mainthread: 1301

Total stream buffers loaded by SQL*Loader loadthread: 3901

Run began on Mon Jan 07 23:21:42 2013

Run ended on Mon Jan 07 23:22:18 2013

Elapsed time was: 00:00:35.91

CPU time was: 00:00:25.79

非并行测试

创建测试表

create table sqlldr_nopal

nologging

PARTITION BY HASH (sfzh)

(partition p1 tablespace users , partition p2tablespace zdry_tbs2 )

AS SELECT *FROM sfxx2 where 1=2 ;

sqlldr_nopar.ctl

LOAD DATA

INFILE 'd:\flatfile\flatfile.dat'

APPEND

INTO TABLEsqlldr_nopar

fields terminated by X'23'

TRAILING NULLCOLS

(zj,

…字段太多省略

cjdwdm)

sqlldr_nopar.par

这里删除了并行选项

USERID=zdrygk/zdrygk

CONTROL='d:\flatfile\sqlldr_nopar.ctl'

DATA='d:\flatfile\flatfile.dat'

LOG='d:\flatfile\sqlldr_nopar.log'

DIRECT=true

ERRORS=10000

执行

D:\flatfile>sqlldrparfile=d:\flatfile\sqlldr_nopar.par

SQL*Loader: Release 11.2.0.1.0 - Production onMon Jan 7 23:20:54 2013

Load completed - logical record count 3022375.

sqlldr_nopar.log

Control File: d:\flatfile\sqlldr_nopar.ctl