使用sqlloader的直接載入方式和傳統載入方式的效能差異

Direct 直接載入特點
（1）資料繞過SGA直接寫入磁碟的資料檔案
（2）資料直接寫入高水位線HWM之後的新塊，不會掃描HWM之前的空閒塊
（3）commit之後移動HWM他人才能看到
（4）不對已用空間進行掃描
（5）使用direct幾乎不產生redo log，不是完全不產生(安全性差)，但會產生undo資料
（6）適用OLAP線上分析場景，增 刪 改不頻繁的場景

Conventional傳統載入特點
（1）資料先載入 -> SGA -> 磁碟的資料檔案
（2）會掃描高水位線HWM之前的資料塊，如果有空閒塊(碎片經常DML導致)就會利用，如果沒有再插入新塊
（3）高水位線HWM之前的資料塊是放在SGA區的
（4）會產生redo log和undo資料
（5）安全性高，可恢復資料
（6）傳統載入與SQL語句insert插入沒區別

1.建立一個文字資料

BYS@ bys001>set termout off;
BYS@ bys001>set feedback off;
BYS@ bys001>set echo off;
BYS@ bys001>set heading off;
BYS@ bys001>set trimout on;
BYS@ bys001>set trimspool on;
BYS@ bys001>spool /home/oracle/sqlload.txt
BYS@ bys001>select owner||','||object_name||','||object_id from dba_objects;
BYS@ bys001>spool off;
此時退出SQLPLUS,到/home/oracle/  下檢視
[oracle@oel-01 ~]$ tail sqlload.txt
SCOTT,TEST_AUDIT,75940
SYS,WRH$_ACTIVE_SESSION_HISTORY,76484
SYS,WRH$_TABLESPACE_STAT,76488
BYS@ bys001>spool off;
[oracle@oel-01 ~]$ vi sqlload.txt
輸入 shift + g 將游標定位到最後一行，刪除BYS@ bys001>spool off;  這一行，儲存退出。
[oracle@oel-01 ~]$ cat sqlload.txt | wc -l
72893

2.資料庫中建立表，OS中建立SQL*LOADER的控制檔案

BYS@ bys001>create table test1(owner varchar2(30),object_name varchar2(150),object_id number);

Table created.
建立控制檔案，內容如下
[oracle@oel-01 ~]$ cat sqlload.ctl
load data
infile '/home/oracle/sqlload.txt'
badfile '/home/oracle/sqlload_bad.txt'
discardfile '/home/oracle/sqlload_discard.txt'
append into table test1
fields terminated by ","
trailing nullcols
(owner,object_name,object_id)

3.開始載入，使用直接載入方法，用時約1秒

[oracle@oel-01 ~]$ sqlldr bys/bys control=sqlload.ctl log=sqlload.log direct=true

SQL*Loader: Release 11.2.0.1.0 - Production on Mon Jul 29 10:10:06 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Load completed - logical record count 72893.
日誌如下：
[oracle@oel-01 ~]$ cat sqlload.log

SQL*Loader: Release 11.2.0.1.0 - Production on Mon Jul 29 10:10:06 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   sqlload.ctl
Data File:      /home/oracle/sqlload.txt
  Bad File:     /home/oracle/sqlload_bad.txt
  Discard File: /home/oracle/sqlload_discard.txt
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      Direct

Table TEST1, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
OWNER                               FIRST     *   ,       CHARACTER            
OBJECT_NAME                          NEXT     *   ,       CHARACTER            
OBJECT_ID                            NEXT     *   ,       CHARACTER            

Record 2: Discarded - all columns null.
Record 1: Rejected - Error on table TEST1, column OBJECT_ID.
ORA-01722: invalid number

Record 10001: Discarded - all columns null.
Record 20000: Discarded - all columns null.
Record 29999: Discarded - all columns null.
Record 39998: Discarded - all columns null.
Record 49997: Discarded - all columns null.
Record 59996: Discarded - all columns null.
Record 69995: Discarded - all columns null.

Table TEST1:
  72884 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  8 Rows not loaded because all fields were null.

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:         72893
Total logical records rejected:         1
Total logical records discarded:        8
Total stream buffers loaded by SQL*Loader main thread:       16
Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Mon Jul 29 10:10:06 2013
Run ended on Mon Jul 29 10:10:07 2013

Elapsed time was:     00:00:00.99
CPU time was:         00:00:00.12
用時約1秒

4.使用傳統載入方法：用時3秒

[oracle@oel-01 ~]$ echo 1>sqlload.log
在SQLPLUS中刪除表中資料。
BYS@ bys001>select count(*) from test1;

  COUNT(*)
----------
     72884

BYS@ bys001>truncate table test1;
[oracle@oel-01 ~]$ sqlldr bys/bys control=sqlload.ctl log=sqlload.log
在SQLPLUS中檢視：
BYS@ bys001>select count(*) from test1;

  COUNT(*)
----------
     72884

[oracle@oel-01 ~]$ cat sqlload.log

SQL*Loader: Release 11.2.0.1.0 - Production on Mon Jul 29 10:12:17 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   sqlload.ctl
Data File:      /home/oracle/sqlload.txt
  Bad File:     /home/oracle/sqlload_bad.txt
  Discard File: /home/oracle/sqlload_discard.txt
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table TEST1, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
OWNER                               FIRST     *   ,       CHARACTER            
OBJECT_NAME                          NEXT     *   ,       CHARACTER            
OBJECT_ID                            NEXT     *   ,       CHARACTER            

Record 2: Discarded - all columns null.
Record 1: Rejected - Error on table TEST1, column OBJECT_ID.
ORA-01722: invalid number

Record 10001: Discarded - all columns null.
Record 20000: Discarded - all columns null.
Record 29999: Discarded - all columns null.
Record 39998: Discarded - all columns null.
Record 49997: Discarded - all columns null.
Record 59996: Discarded - all columns null.
Record 69995: Discarded - all columns null.

Table TEST1:
  72884 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  8 Rows not loaded because all fields were null.


Space allocated for bind array:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:         72893
Total logical records rejected:         1
Total logical records discarded:        8

Run began on Mon Jul 29 10:12:17 2013
Run ended on Mon Jul 29 10:12:20 2013

Elapsed time was:     00:00:02.97
CPU time was:         00:00:00.35