sqlldr大量匯入匯出資料測試

sqlldr是在處理大資料量的操作中建議採用的方式，它有許多效能想關的開關，能最大程度的減少redo,undo的產生，控制資料的處理方式(insert,append,replace,truncate)

因為項目需要，對比datapump效能還是不理想，所以還是希望採用sqlldr來做。個人做了簡單的測試。

根據thomas kyte的介紹，並存執行路徑載入時最快的方式，能夠直接寫只格式化的資料區塊，最大限度的減少redo,undo的產生。

Linux環境下sqlldr一個csv檔案

sqlldr負載檔案，ORA-01722: invalid number解決

Oracle sqlldr匯入以斷行符號和斷行符號換行字元結尾的文本

sqlldr 匯入亂碼 Oracle用戶端字元集問題

Oracle sqlldr匯入資料使用預設值和提高效能的方法

先寫了如下的指令碼。可以動態從某個使用者的表中產生中繼資料。
 
sqlplus -s $1 <<EOF
 set pages 0
 col object_name format a30
 set linseize 10000
 set feedback off
 set colsep ','
 spool $2.lst
 select *from $2 ;
 spool off;
 EOF
 
運行後產生的資料大體如下。
 [ora11g@rac1 sqlldr]$ ksh spooldata.sh n1/n1 t
    370753,    10205,KU$_DOMIDX_OBJNUM_VIEW        ,VIEW
    370754,    10207,KU$_OPTION_OBJNUM_T          ,TYPE
    370755,    10208,KU$_EXPREG                    ,VIEW
    370756,    10210,SYS_YOID0000010209$          ,TYPE
    370757,    10209,KU$_OPTION_OBJNUM_VIEW        ,VIEW
    370758,    10211,KU$_OPTION_VIEW_OBJNUM_VIEW  ,VIEW
    370759,    10212,KU$_MARKER_T                  ,TYPE
    370760,    10214,SYS_YOID0000010213$          ,TYPE
    370761,    10213,KU$_MARKER_VIEW              ,VIEW
    370762,    10215,KU$_TABPROP_VIEW              ,VIEW
    370763,    10216,KU$_PFHTABPROP_VIEW          ,VIEW
    370764,    10217,KU$_REFPARTTABPROP_VIEW      ,VIEW
    370765,    10218,KU$_MVPROP_VIEW              ,VIEW
    370766,    10219,KU$_MVLPROP_VIEW              ,VIEW
    370767,    10220,KU$_TTS_VIEW                  ,VIEW
    370768,    10221,KU$_TAB_TS_VIEW              ,VIEW
    370769,    10222,KU$_TTS_IND_VIEW              ,VIEW
    370770,    10223,KU$_IND_TS_VIEW              ,VIEW
    370771,    10224,KU$_CLU_TS_VIEW              ,VIEW
 
然後準備控制檔案 sqlldr.ctl,把資料從t載入到tt裡面去。
 load data
 into table tt
 fields terminated by ','
 (id,object_id,object_name,object_type)
 
嘗試匯入：
 [ora11g@rac1 sqlldr]$ sqlldr n1/n1 control=sqlldr.ctl data=t.lst
 SQL*Loader: Release 11.2.0.3.0 - Production on Tue May 27 08:09:25 2014
 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 但是沒有任何的反饋。
 查看自動產生的sqlldr.log
 裡面有如下的錯誤。
 
  Column Name                  Position  Len  Term Encl Datatype
 ------------------------------ ---------- ----- ---- ---- ---------------------
 ID                                  FIRST    *  ,      CHARACTER           
 OBJECT_ID                            NEXT    *  ,      CHARACTER           
 OBJECT_NAME                          NEXT    *  ,      CHARACTER           
 OBJECT_TYPE                          NEXT    *  ,      CHARACTER           
 

Record 1: Rejected - Error on table TT, column OBJECT_TYPE.
 Field in data file exceeds maximum length
 Record 2: Rejected - Error on table TT, column OBJECT_TYPE.
 Field in data file exceeds maximum length
 Record 3: Rejected - Error on table TT, column OBJECT_TYPE.
 Field in data file exceeds maximum length
 Record 4: Rejected - Error on table TT, column OBJECT_TYPE.
 Field in data file exceeds maximum length
 
嘗試了好一會兒，最終發現時set linesize的時候長度設定的比較大，在根據逗號','來解析的時候，最後一個欄位的長度就包含了剩餘的空格，最終載入的時候就會發現它的長度太大了。已經超出了表定義的長度。
 這種情況，我總不能一個一個指定長度吧。
 這時候想到trimspool的功能，嘗試果然奏效。
 spooldata.sh的指令碼內容如下：
 sqlplus -s $1 <<EOF
 set pages 0
 col object_name format a30
 set linesize 10000
 set trimspool on
 set feedback off
 set colsep ','
 spool $2.lst
 select *from $2 where rownum<20 ;
 spool off;
 EOF
 

再次嘗試匯入，就沒有問題了。
 [ora11g@rac1 sqlldr]$ sqlldr n1/n1 control=sqlldr.ctl data=t.lst
 SQL*Loader: Release 11.2.0.3.0 - Production on Tue May 27 08:14:44 2014
 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 Commit point reached - logical record count 19
 
到此為止，來開始看看direct方式到底有多大的效能提升
 對於將近80萬的資料量測試情況如下。
 沒有採用direct方式的時候，會有一定的頻度（預設50條一次)來載入資料，耗時79秒，基本一秒一萬條
 Commit point reached - logical record count 793480
 Commit point reached - logical record count 793544
 Commit point reached - logical record count 793608
 Commit point reached - logical record count 793672
 Commit point reached - logical record count 793736
 Commit point reached - logical record count 793800
 Commit point reached - logical record count 793864
 Commit point reached - logical record count 793928
 Commit point reached - logical record count 793992
 Commit point reached - logical record count 794056
 Commit point reached - logical record count 794120
 Commit point reached - logical record count 794184
 Commit point reached - logical record count 794248
 Commit point reached - logical record count 794312
 Commit point reached - logical record count 794369
 
但是使用了direct=true的時候，速度明顯提升，而且輸出也很簡單，就下面一行。耗時8秒，基本一秒10萬條資料。
 8s
 [ora11g@rac1 sqlldr]$ sqlldr n1/n1 direct=true control=sqlldr.ctl data=t.lst   
 SQL*Loader: Release 11.2.0.3.0 - Production on Tue May 27 07:56:31 2014
 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 Load completed - logical record count 794369.