加载中…
个人资料
微博
  • 博客等级：
  • 博客积分：
  • 博客访问：
  • 关注人气：
  • 获赠金笔：0支
  • 赠出金笔：0支
  • 荣誉徽章：
正文 字体大小：大 中 小

sqlloader加载数据的实验

(2010-12-26 21:20:35)
标签：

oracle

原创

实验记录

sqlldr

杂谈

 分类： oracle实验笔记

生成导入的文本文件

[oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ cat getobject.sql
set echo off
set term off
set line 1000 pages 0
set feedhack off
set heading off
spool ldr_object.txt
select a.owner||',"'||a.object_name||'",'||a.object_id||','||
       to_char(a.created,'yyyy-mm-dd hh24:mi:ss')|| ','||a.status
from dba_objects a,(select rownum rn from dual connect by rownum<=10) b;

spool off
set heading on
set feedback on
set term on
set echo on
[oracle@localhost ~]$

执行
SYS@PENG SQL> @getobject.sql

[oracle@localhost ~]$ ll
总计 31244
-rw-r--r-- 1 oracle oinstall       83 12-07 04:49 analyze_index.sql
-rw-r--r-- 1 oracle oinstall      369 12-27 06:37 getobject.sql
-rw-r--r-- 1 oracle oinstall      195 12-27 06:21 ldr_object.ctl
-rw-r--r-- 1 oracle oinstall 31941175 12-27 07:39 ldr_object.txt
[oracle@localhost ~]$ wc -l ldr_object.txt
502524 ldr_object.txt
生成的文件50万行。

创建导入的表,用户scott

SCOTT@PENG SQL> drop table objects purge;
SQL> create table objects(
  2  owner varchar2(30),
  3  object_name varchar2(50),
  4  object_id number,
  5  status varchar2(10),

  6  created date);

Table created.

Elapsed: 00:00:00.18

SQL> create index idx_obj_owner_name on objects(owner,object_name);

Index created.

Elapsed: 00:00:00.33
SQL>

创建控制文件：ldr_object.ctl

load data

infile ldr_object.txt

truncate into table objects

fields terminated by "," optionally enclosed by '"'

(owner,

 object_name,

 object_id,

 created date 'yyyy-mm-dd hh24:mi:ss',

 status

)

 

[oracle@localhost ~]$ ll
总计 31244
-rw-r--r-- 1 oracle oinstall       83 12-07 04:49 analyze_index.sql
-rw-r--r-- 1 oracle oinstall      369 12-27 06:37 getobject.sql
-rw-r--r-- 1 oracle oinstall      195 12-27 07:47 ldr_object.ctl
-rw-r--r-- 1 oracle oinstall 31941175 12-27 07:39 ldr_object.txt
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlldr control=ldr_object.ctl errors=10
Username:scott
Password:
SQL*Loader: Release 10.2.0.4.0 - Production on Mon Dec 27 07:54:02 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Commit point reached - logical record count 64

Commit point reached - logical record count 128
Commit point reached - logical record count 192

查日志文件
Table OBJECTS:
  502520 Rows successfully loaded.
  4 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:                  82560 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:        502524
Total logical records rejected:         4
Total logical records discarded:        0

Run began on Mon Dec 27 07:47:55 2010
Run ended on Mon Dec 27 07:50:24 2010

Elapsed time was:     00:01:26.31
CPU time was:         00:00:12.52

 

提高导入时一次加载的行数

[oracle@localhost ~]$ sqlldr scott/triger control=ldr_object.ctl errors=10 rows=640
查看日志

value used for ROWS parameter changed from 640 to 198
Table OBJECTS:
  502520 Rows successfully loaded.
  4 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:                 255420 bytes(198 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:        502524
Total logical records rejected:         4
Total logical records discarded:        0

Run began on Mon Dec 27 07:59:44 2010
Run ended on Mon Dec 27 08:01:20 2010

Elapsed time was:     00:00:54.78
CPU time was:         00:00:08.25

 

提高sindsize的大小到10m 和一次加载5000条记录

[oracle@localhost ~]$ sqlldr scott/triger control=ldr_object.ctl errors=10 rows=5000 bindsize=10485760

SQL*Loader: Release 10.2.0.4.0 - Production on Mon Dec 27 08:07:09 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

specified value for readsize(1048576) less than bindsize(10485760)
Commit point reached - logical record count 5000
Commit point reached - logical record count 10000
Commit point reached - logical record count 15000
查看日志

Table OBJECTS:
  502520 Rows successfully loaded.
  4 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:                6450000 bytes(5000 rows)
Read   buffer bytes:10485760

Total logical records skipped:          0
Total logical records read:        502524
Total logical records rejected:         4
Total logical records discarded:        0

Run began on Mon Dec 27 08:07:09 2010
Run ended on Mon Dec 27 08:08:09 2010

Elapsed time was:     00:00:33.91
CPU time was:         00:00:05.47

 

使用默认参数，开启直接路径加载

[oracle@localhost ~]$ sqlldr scott/triger control=ldr_object.ctl errors=10 direct=true

SQL*Loader: Release 10.2.0.4.0 - Production on Mon Dec 27 08:15:02 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Load completed - logical record count 502524.
[oracle@localhost ~]$
查看日志

Table OBJECTS:
  502520 Rows successfully loaded.
  4 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.

  Date conversion cache disabled due to overflow (default size: 1000)

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:        502524
Total logical records rejected:         4
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:      122
Total stream buffers loaded by SQL*Loader load thread:       67

Run began on Mon Dec 27 08:15:02 2010
Run ended on Mon Dec 27 08:15:23 2010

Elapsed time was:     00:00:12.22
CPU time was:         00:00:02.33

 

再次提高，使用之前的参数和直接路径加载

[oracle@localhost ~]$ sqlldr scott/triger control=ldr_object.ctl errors=10 rows=5000 bindsize=10485760 direct=true

SQL*Loader: Release 10.2.0.4.0 - Production on Mon Dec 27 08:19:45 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Save data point reached - logical record count 5000.
Save data point reached - logical record count 10000.
Save data point reached - logical record count 15000.
Save data point reached - logical record count 20000.
查看日志

Table OBJECTS:
  502520 Rows successfully loaded.
  4 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.

  Date conversion cache disabled due to overflow (default size: 1000)

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:        502524
Total logical records rejected:         4
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:      131
Total stream buffers loaded by SQL*Loader load thread:       53

Run began on Mon Dec 27 08:19:45 2010
Run ended on Mon Dec 27 08:20:07 2010

Elapsed time was:     00:00:11.52
CPU time was:         00:00:02.57

从最开始的

Elapsed time was:     00:01:26.31
CPU time was:         00:00:12.52

到最后的

Elapsed time was:     00:00:11.52
CPU time was:         00:00:02.57
效果非常明显。

 

喜欢

0

阅读┊ 收藏 ┊ 喜欢▼ ┊打印┊举报/Report
  

新浪BLOG意见反馈留言板　欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

Copyright © 1996 - 2022 SINA Corporation, All Rights Reserved

新浪公司 版权所有