加载中…ORACLE SQL LOADER加载大文件及加载优化示例
(2012-12-21 11:35:46)
标签：
oraclesqlldr数据导入杂谈 |
分类： ORACLE |
一.创建表
SQL> create table csdn642 (id varchar2(100),password varchar2(198),mail varchar2(100));
Table created.
SQL>
二.数据文件
[oracle@ora10g sqlldr]$ head csdn.dat
zdg # 12344321 # zdg@csdn.net
LaoZheng # 670203313747 # chengming_zheng@163.com
fstao # 730413 # fstao@tom.com
huwolf # 2535263 # hujiye@263.net
cadcjl # KIC43dk6! # ccedcjl@21cn.com
netsky # s12345 # songmail@21cn.com
Michael # apple # appollp@netease.com
siclj # lj7202 # junlu@peoplemail.com.cn
jinbuhuan # 12345 # jinbuhuan@163.net
Eie # hebeibdh # fwg@jxfw.com
[oracle@ora10g sqlldr]$ wc -l csdn.dat
6428632 www.csdn.net.sql
[oracle@ora10g sqlldr]$
三.控制文件
[oracle@ora10g sqlldr]$ cat sample9.ctl
LOAD DATA
INFILE csdn.dat
TRUNCATE INTO TABLE CSDN642
FIELDS TERMINATED BY '#'
(ID,PASSWORD,MAIL)
[oracle@ora10g sqlldr]$
四.第一次加载
#常规加载
1.执行加载
[oracle@ora10g sqlldr]$ sqlldr SCOTT/TIGER control=sample9.ctl
2.查看日志
[oracle@ora10g sqlldr]$ cat sample9.log
SQL*Loader: Release 10.2.0.4.0 - Production on Wed Mar 28 23:42:51 2012
Copyright (c) 1982, 2007, Oracle.
Control File:
Data
File:
Bad File:
Discard File:
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind
array:
Continuation:
Path
used:
Table CSDN642, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
Column
Name
------------------------------ ---------- ----- ---- ---- ---------------------
ID
PASSWORD
MAIL
Table CSDN642:
6428632 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind
array:
Read
Total logical records
skipped:
Total logical records
read:
Total logical records
rejected:
Total logical records
discarded:
Run began on Wed Mar 28 23:42:51 2012
Run ended on Wed Mar 28 23:45:39 2012
Elapsed time
was:
CPU time
was:
[oracle@ora10g sqlldr]$
#耗时2分47秒
五.第二次加载
#提升默认加载的行数.指定rows参数
#SQL LOADER常规路径导入时默认一次加载64行.
1.执行加载
[oracle@ora10g sqlldr]$ sqlldr SCOTT/TIGER control=sample9.ctl rows=600
2.查看日志
[oracle@ora10g sqlldr]$ cat sample9.log
SQL*Loader: Release 10.2.0.4.0 - Production on Wed Mar 28 23:47:22 2012
Copyright (c) 1982, 2007, Oracle.
Control File:
Data
File:
Bad
File:
Discard File:
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind
array:
Continuation:
Path
used:
Table CSDN642, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
Column
Name
------------------------------ ---------- ----- ---- ---- ---------------------
ID
PASSWORD
MAIL
value used for ROWS parameter changed from 600 to 330
Table CSDN642:
6428632 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind
array:
Read
Total logical records
skipped:
Total logical records
read:
Total logical records
rejected:
Total logical records
discarded:
Run began on Wed Mar 28 23:47:22 2012
Run ended on Wed Mar 28 23:48:19 2012
Elapsed time
was:
CPU time
was:
[oracle@ora10g sqlldr]$
#耗时56秒.
#rows值实际为330.bindsize限制了它.
六.第三次加载
#调整bindsize. 默认256K
#bindsize指定绑定数组大小
#再次提升rows
1.执行导入
[oracle@ora10g sqlldr]$ sqlldr SCOTT/TIGER control=sample9.ctl rows=10000 bindsize=30720000
2.查看日志
[oracle@ora10g sqlldr]$ cat sample9.log
SQL*Loader: Release 10.2.0.4.0 - Production on Wed Mar 28 23:50:42 2012
Copyright (c) 1982, 2007, Oracle.
Control File:
Data
File:
Bad
File:
Discard File:
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind
array:
Continuation:
Path
used:
Table CSDN642, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
Column
Name
------------------------------ ---------- ----- ---- ---- ---------------------
ID
PASSWORD
MAIL
Table CSDN642:
6428632 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind
array:
Read
Total logical records
skipped:
Total logical records
read:
Total logical records
rejected:
Total logical records
discarded:
Run began on Wed Mar 28 23:50:42 2012
Run ended on Wed Mar 28 23:51:10 2012
Elapsed time
was:
CPU time
was:
[oracle@ora10g sqlldr]$
#耗时28秒.
七.第四次加载
#直接路径加载
1.执行导入
[oracle@ora10g sqlldr]$ sqlldr SCOTT/TIGER control=sample9.ctl direct=true
SQL*Loader: Release 10.2.0.4.0 - Production on Wed Mar 28 23:52:53 2012
Copyright (c) 1982, 2007, Oracle.
Load completed - logical record count 6428632.
#直接路径加载默认读取全部记录,读取到的数据处理后存入流缓存区.
#流缓存区默认256K
2.查看日志
[oracle@ora10g sqlldr]$ cat sample9.log
SQL*Loader: Release 10.2.0.4.0 - Production on Wed Mar 28 23:52:53 2012
Copyright (c) 1982, 2007, Oracle.
Control File:
Data
File:
Bad
File:
Discard File:
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:
Path
used:
Table CSDN642, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
Column
Name
------------------------------ ---------- ----- ---- ---- ---------------------
ID
PASSWORD
MAIL
Table CSDN642:
6428632 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Bind array size not used in direct path.
Column array
Stream buffer bytes:
Read
Total logical records
skipped:
Total logical records
read:
Total logical records
rejected:
Total logical records
discarded:
Total stream buffers loaded by SQL*Loader main
thread:
Total stream buffers loaded by SQL*Loader load
thread:
Run began on Wed Mar 28 23:52:53 2012
Run ended on Wed Mar 28 23:53:18 2012
Elapsed time
was:
CPU time
was:
[oracle@ora10g sqlldr]$
#耗时24秒
八.第五次加载
#直接路径加载的优化:
1.执行导入
[oracle@ora10g sqlldr]$ sqlldr SCOTT/TIGER control=sample9.ctl direct=true streamsize=30720000
SQL*Loader: Release 10.2.0.4.0 - Production on Wed Mar 28 23:54:27 2012
Copyright (c) 1982, 2007, Oracle.
Load completed - logical record count 6428632.
2.查看日志
[oracle@ora10g sqlldr]$ cat sample9.log
SQL*Loader: Release 10.2.0.4.0 - Production on Wed Mar 28 23:54:27 2012
Copyright (c) 1982, 2007, Oracle.
Control File:
Data
File:
Bad
File:
Discard File:
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:
Path
used:
Table CSDN642, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
Column
Name
------------------------------ ---------- ----- ---- ---- ---------------------
ID
PASSWORD
MAIL
Table CSDN642:
6428632 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Bind array size not used in direct path.
Column array
Stream buffer bytes:16777216
Read
Total logical records
skipped:
Total logical records
read:
Total logical records
rejected:
Total logical records
discarded:
Total stream buffers loaded by SQL*Loader main
thread:
Total stream buffers loaded by SQL*Loader load
thread:
Run began on Wed Mar 28 23:54:27 2012
Run ended on Wed Mar 28 23:54:50 2012
Elapsed time
was:
CPU time
was:
[oracle@ora10g sqlldr]$
#耗时22秒
THE END
喜欢
0
赠金笔