【原創】比較使用sql*loader的直接載入方式和傳統載入方式的效能差異

二比較使用sql*loader的直接載入方式和傳統載入方式的效能差異，給出演示過程和結論。

第一 我們先要生成平面資料（文字資料）

LEO1@LEO1>create table leo2 as select *from dba_objects;      建立資料來源，我們的平面資料就是從這個表中取出

Table created.

第二 我們利用spool工具將螢幕中顯示出來的記錄寫入到指定檔案，這樣我們就可以得到一個平面檔案啦

set termout off;            是否在螢幕上顯示輸出內容，off螢幕不顯示查詢語句，主要與spool結合使用

set feedback off;           關閉本次sql命令處理的記錄條數，預設為on即去掉最後的已經選擇的行數

set echo off;              關閉指令碼中正在執行的SQL語句的顯示

set heading off;            關閉標題的輸出，設定為off就去掉了select結果的欄位名只顯示資料

set trimout on;            去除標準輸出每行後面多餘的空格

set trimspool on;          將每行後面多餘的空格去掉【linesize-實際字元數=多餘空格】

spool /home/oracle/sql_loader/leo3.txt      在螢幕上的所有內容都包含在該檔案中

select owner||','||object_name||','||object_id||','||object_typefrom leo2;   

spool off                         只有關閉spool輸出，才會在輸出檔案中看到輸出的內容

備註：在實用SPOOL輸出內容到本地檔案時，需注意編碼格式，否則會出現亂碼的問題

[oracle@leonarding1 sql_loader]$ ll

total 28468

-rw-r--r-- 1 oracle oinstall  3246601 Jun 22 14:06 leo3.txt          已經生成平面檔案leo3.txt

[oracle@leonarding1 sql_loader]$ cat leo3.txt| wc -l              檔案中有72678行記錄

72678

第三 建立裝入的表leo3_loader

LEO1@LEO1>create table leo3_loader

(

  owner       varchar2(30),

  object_name varchar2(130),

  object_id   number,

  object_type varchar2(20)

);

2    3    4   5    6    7  

第四 建立sql*loader的控制檔案leo3_loader.ctl

[oracle@leonarding1 sql_loader]$ vim leo3_loader.ctl

load data

infile '/home/oracle/sql_loader/leo3.txt'                             待載入的資料檔案

badfile '/home/oracle/sql_loader/leo3_bad.txt'                       格式不匹配寫入壞檔案

discardfile'/home/oracle/sql_loader/leo3_discard.txt'                  條件不匹配寫入丟棄檔案

append into table leo3_loader                                     追加的方式插入資料

fields terminated by ","                                           欄位與欄位之間的分隔符

trailing nullcols                                                 這句的意思是將沒有對應值的列都置為null

(owner,object_name,object_id,object_type)                         資料插入的對應欄位

第五 執行sqlldr直接載入命令

[oracle@leonarding1 sql_loader]$ sqlldrleo1/leo1 control=leo3_loader.ctl log=leo3_loader.log direct=true

SQL*Loader: Release 11.2.0.1.0 - Productionon Sat Jun 22 14:08:31 2013

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

Load completed - logical record count72678.

已經載入了72678行，條件不匹配有72行，實際載入入72606行

LEO1@LEO1>select count(*) fromleo3_loader;   表中也是

    72606

我們在看一下sql*loader日誌

。。。省略前部份。。。

Total logical records skipped:          0

Total logical recordsread:         72678

Total logical records rejected:         0

Total logical records discarded:       72                   條件不匹配有72行

Total stream buffers loaded by SQL*Loadermain thread:       17

Total stream buffers loaded by SQL*Loaderload thread:        6

Run began on Sat Jun 22 14:08:31 2013

Run ended on Sat Jun 22 14:08:34 2013

Elapsed time was:     00:00:02.60                       所用耗時2.6秒

CPU time was:         00:00:00.13

使用conventional傳統載入方式寫入資料

LEO1@LEO1>truncate table leo3_loader;                   清空表在載入一次

[oracle@leonarding1 sql_loader]$ sqlldrleo1/leo1 control=leo3_loader.ctl log=leo3_loader.log

LEO1@LEO1>select count(*) fromleo3_loader;   表中也是

    72606

我們在看一下sql*loader日誌

。。。省略前部份。。。

Total logical records skipped:          0

Total logical recordsread:         72678

Total logical records rejected:         0

Total logical records discarded:       72                  條件不匹配有72行

Run began on Sat Jun 22 15:25:45 2013

Run ended on Sat Jun 22 15:26:05 2013

Elapsed time was:     00:00:20.79                       所用耗時2.6秒

CPU time was:         00:00:00.48

小結：經過比對direct比conventional要提高了20倍效率，為什麼direct會這麼高效呢，下面我們來說說這兩種的區別。

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插入沒區別

2013.6.22
北京&summer
分享技術~成就夢想
Blog：