sqlldr并發

sage: SQLLDR keyword=value [,keyword=value,...]

部分關鍵字:

    userid -- ORACLE username/password

   control -- 控制檔案

       log -- 記錄的日志檔案

       bad -- 壞資料檔案

      data -- 資料檔案

   discard -- 丢棄的資料檔案

discardmax -- 允許丢棄資料的最大值        (預設全部)

      skip -- Number of logical records to skip  (預設0)

      load -- Number of logical records to load  (預設全部)

    errors -- 允許的錯誤記錄數          (預設50)

      rows --（每次送出的記錄數，預設: 正常路徑 64, 直接路徑 全部，是以使用直接路徑的話，效率會比普通的好太多太多）

  bindsize --（ 每次送出記錄的緩沖區的大小，位元組為機關，預設256000)

    silent -- 禁止輸出資訊 (header,feedback,errors,discards,partitions)

    direct -- 使用直通路徑方式導入 (預設FALSE)

   parfile -- parameter file: name of file that contains parameter specifications

  parallel -- 并行導入 

1) ROWS 的預設值為 64，你可以根據實際指定更合适的 ROWS 參數來指定每次送出記錄數。(體驗過在 PL/SQL Developer 中一次執行幾條條以上的 insert 語句的情形嗎？)

2）正常導入可以通過使用 INSERT語句來導入資料。Direct導入可以跳過資料庫的相關邏輯(DIRECT=TRUE)，而直接将資料導入到資料檔案中，可以提高導入資料的性能。當然，在很多情況下，不能使用此參數(如果主鍵重複的話會使索引的狀态變成UNUSABLE!)。

3) 通過指定 UNRECOVERABLE選項，可以關閉資料庫的日志(是否要 alter table table1 nologging 呢?)。這個選項隻能和 direct 一起使用。

4) 對于超大資料檔案的導入就要用并發操作了，即同時運作多個導入任務.

  sqlldr   userid=/   control=result1.ctl   direct=true   parallel=true  

  sqlldr   userid=/   control=result2.ctl   direct=true   parallel=true  

  當加載大量資料時（大約超過10GB），最好抑制日志的産生：  

  SQL>ALTER   TABLE   RESULTXT   nologging;

  這樣不産生REDO LOG，可以提高效率。然後在 CONTROL 檔案中 load data 上面加一行：unrecoverable，  此選項必須要與DIRECT共同應用。  

Maximizing SQL*Loader Performance 

SQL*Loader is flexible and offers many options that should be considered to maximize the speed of data loads.  These include many permutations of the SQL*Loader control file parameters:

OPTIONS (DIRECT=TRUE, ERRORS=50, rows=500000) 

UNRECOVERABLE LOAD DATA 

- Use Direct Path Loads - The conventional path loader essentially loads the data by using standard insert statements.  The direct path loader (direct=true) loads directly into the Oracle data files and creates blocks in Oracle database block format.  To prepare the database for direct path loads, the script$ORACLE_HOME/rdbms/admin/catldr.sql.sql must be executed.

- Disable Indexes and Constraints.  For conventional data loads only, the disabling of indexes and constraints can greatly enhance the performance of SQL*Loader.  The skip_index_maintenance SQL*Loader parameter allows you to bypass index maintenance when performing parallel build data loads into Oracle, but only when using the sqlldr direct=y direct load options. 

According to Dave More in his book 'Oracle Utilities' usingskip_index_maintenance=true means 'don't rebuild indexes', and it will greatly speed-up sqlldr data loads when using parallel processes with sqlldr:

Also, according to Oracle expert Jonathan Gennick "Theskip_index_maintenance SQL*Loader parameter: 'Controls whether or not index maintenance is done for a direct path load. This parameter does not apply to conventional path loads. A value of TRUE causes index maintenance to be skipped.

- Use a Larger Bind Array.  For conventional data loads only, larger bind arrays limit the number of calls to the database and increase performance.  The size of the bind array is specified using thebindsize parameter.  The bind array's size is equivalent to the number of rows it contains (rows=) times the maximum length of each row.

- Increase the input data buffer - The sqlldr readsize parameter determines the input data buffer size used by SQL*Loader

- Use ROWS=n to Commit Less Frequently.  For conventional data loads only, rows specifies the number of rows per commit.  Issuing fewer commits will enhance performance. 

- Use Parallel Loads.  Available with direct path data loads only, this option allows multiple SQL*Loader jobs to execute concurrently. Note:  You must be on an SMP server (cpu_count > 2 at least) to successfully employ parallelism, and you must also employ the append option, else you may get this error:  "SQL*Loader-279: Only APPEND mode allowed when parallel load specified."

Note that you can also run SQL*Loader in parallel, and create parallel parallelism:

$ sqlldr control=first.ctl  parallel=true direct=true 

$ sqlldr control=second.ctl parallel=true direct=true

6.   Use Fixed Width Data.  Fixed width data format saves Oracle some processing when parsing the data. 

7.   Disable Archiving During Load.  While this may not be feasible in certain environments, disabling database archiving can increase performance considerably.

8.   Use unrecoverable.  The unrecoverable option (unrecoverable load data) disables the writing of the data to the redo logs.  This option is available for direct path loads only.

Related SQL*Loader Articles:

Maximizing SQL*Loader Performance

Hypercharge SQL*Loader load speed performance

Loading large datasets with SQL*Loader

See complete sqlldr directions here:

有一個錯誤情況是

SQL*Loader-951:  調用一次/加載初始化錯誤
ORA-00604: 遞歸 SQL 級别 1 出現錯誤
ORA-00054: 資源正忙, 但指定以 NOWAIT 方式擷取資源, 或者逾時失效      

去掉direct=true即可

sqlldr scott/tiger control=multiplefile.ctl log=multiplefile.log bindsize=10000000 readsize=
10000000  rows=5000      

bindsize和readsize是設定緩沖區大小


ctl問卷模版      

options(skip=1)
unrecoverable
load data
characterset utf8
append into table MI_QUESTIONNAIRE_20161011
FIELDS TERMINATED BY "," 
TRAILING NULLCOLS
(
Q_DATE date "yyyy_mm_dd" nullif (RECORD_DATE="null"),
RECORD_DATE  date "yyyy-mm-dd hh24:mi:ss" nullif (RECORD_DATE="null"),
DBNAME,
ACOUNT_ID,
ROLE_ID,
Q_TYPE
)
      

sqlldr 使用者名/密碼@伺服器 control=控制檔案.ctl data=資料檔案 errors=10000000 direct=true parallel=true