sqlldr批量匯入匯出資料測試
sqlldr是在處理大資料量的操作中建議採用的方式,它有許多效能想關的開關,能最大程度的減少redo,undo的生成,控制資料的處理方式(insert,append,replace,truncate)
因為專案需要,對比datapump效能還是不理想,所以還是希望採用sqlldr來做。個人做了簡單的測試。
根據thomas kyte的介紹,並行執行路徑載入時最快的方式,能夠直接寫只格式化的資料塊,最大限度的減少redo,undo的生成。
先寫了如下的指令碼。可以動態的從某個使用者的表中生成後設資料。
sqlplus -s $1 <
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 <
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.
因為專案需要,對比datapump效能還是不理想,所以還是希望採用sqlldr來做。個人做了簡單的測試。
根據thomas kyte的介紹,並行執行路徑載入時最快的方式,能夠直接寫只格式化的資料塊,最大限度的減少redo,undo的生成。
先寫了如下的指令碼。可以動態的從某個使用者的表中生成後設資料。
sqlplus -s $1 <
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 <
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.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23718752/viewspace-1171346/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- sqlldr批次匯入匯出資料測試SQL
- 【匯入匯出】sqlldr 匯入案例SQL
- 資料匯入SQLLDRSQL
- 【匯入匯出】sqlldr 匯入含有內嵌換行符的資料SQL
- sqlldr 匯入資料範例SQL
- Oracle 的資料匯入匯出及 Sql Loader (sqlldr) 的用法OracleSQL
- MySQL資料匯入匯出牛刀小試MySql
- 使用csv批量匯入、匯出資料的需求處理
- 通過SQLLDR匯入LOB資料SQL
- 使用EXPDP/IMPDP匯入匯出表中資料/後設資料測試
- 關於 Oracle 的資料匯入匯出及 Sql Loader (sqlldr) 的用法OracleSQL
- 資料泵匯出匯入
- Oracle 資料匯入匯出Oracle
- mysql資料匯入匯出MySql
- mysql資料匯出匯入MySql
- oracle資料匯入匯出Oracle
- oracle sqlldr匯入OracleSQL
- Redis批量匯入文字資料Redis
- 資料庫 MySQL 資料匯入匯出資料庫MySql
- 批量備份還原匯入與匯出MongoDB資料方式昝璽MongoDB
- logstash匯入movielens測試資料
- 極速匯入elasticsearch測試資料Elasticsearch
- sqoop資料匯入匯出OOP
- 資料匯入匯出EXP/IMP
- MongoDB資料匯入與匯出MongoDB
- mysqldump匯入匯出表資料MySql
- exp/imp匯出匯入資料
- postgresql 資料匯入和匯出SQL
- 資料庫的匯入匯出資料庫
- 資料泵的匯入匯出
- mysql 資料庫匯入匯出MySql資料庫
- 資料泵匯出匯入表
- MySQL資料庫匯入匯出MySql資料庫
- 使用oracle sqlldr匯入文字資料的例子OracleSQL
- oracle資料庫使用sqlldr命令匯入txt資料Oracle資料庫SQL
- Oracle資料庫匯入匯出。imp匯入命令和exp匯出命令Oracle資料庫
- MySQL 批量匯入資料優化MySql優化
- MySQL入門--匯出和匯入資料MySql