1. setp-1 生成数据文件
getobject.sql
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 <= 23) b;
call.sql
SET echo off
SET term off
SET line 100 pages 0
SET feedback off
SET heading off
spool d:\oracle\script\ldr_object.csv
@d:\oracle\script\getobject.sql
spool off
SET heading on
SET feedback on
SET term on
SET echo on
执行SQL
SQL> @d:\oracle\script\call.sql
2. step 2 初始化环境:
createobject.sql
CREATE TABLE objects (
owner varchar(30),
object_name varchar(50),
object_id NUMBER,
created date,
status VARCHAR2(10)
);
CREATE INDEX idx_obj_owner_name on objects(owner,object_name);
step 3:第一次执行导入
ldr_object.ctl
load data
infile ldr_object.csv
truncate into table objects
fields terminated by "," optionally enclosed by '"'
(
owner,
object_name,
object_id,
created date 'yyyy-mm-dd hh24:mi:ss',
status "substr(:status,1,5)"
)
执行导入
D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10
查看日志:
ldr_object.log
经过时间为: 00: 01: 47.00
CPU 时间为: 00: 00: 07.43
step 2: 第二次执行导入
D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10 rows=640
查看日志:
ldr_object.log
经过时间为: 00: 01: 25.66
CPU 时间为: 00: 00: 05.75
setp 3: 第三次执行导入
使用direct参数
D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10 direct=true
查看日志:
ldr_object.log
经过时间为: 00: 00: 31.95
CPU 时间为: 00: 00: 03.92
setp 4: 执行第四次导入
加大流存储区,加大日期格式缓冲区
D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10 direct=true streamsize=10485760 date_cache=5000
查看日志:
ldr_object.log
经过时间为: 00: 00: 15.49
CPU 时间为: 00: 00: 03.26
其他参数比较
1. 调整bindsize参数值默认为256K,修改为10M, 同时将一次加载的行数提高到5000
D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10 bindsize=10485760 rows=5000
查看日志:
ldr_object.log
经过时间为: 00: 00: 55.58
CPU 时间为: 00: 00: 05.19
2. 去掉索引
-- Drop indexes
drop index IDX_OBJ_OWNER_NAME;
加大流存储区,加大日期格式缓冲区
D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10 direct=true streamsize=10485760 date_cache=5000
查看日志:
ldr_object.log
经过时间为: 00: 00: 25.45
CPU 时间为: 00: 00: 03.34
getobject.sql
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 <= 23) b;
call.sql
SET echo off
SET term off
SET line 100 pages 0
SET feedback off
SET heading off
spool d:\oracle\script\ldr_object.csv
@d:\oracle\script\getobject.sql
spool off
SET heading on
SET feedback on
SET term on
SET echo on
执行SQL
SQL> @d:\oracle\script\call.sql
2. step 2 初始化环境:
createobject.sql
CREATE TABLE objects (
owner varchar(30),
object_name varchar(50),
object_id NUMBER,
created date,
status VARCHAR2(10)
);
CREATE INDEX idx_obj_owner_name on objects(owner,object_name);
step 3:第一次执行导入
ldr_object.ctl
load data
infile ldr_object.csv
truncate into table objects
fields terminated by "," optionally enclosed by '"'
(
owner,
object_name,
object_id,
created date 'yyyy-mm-dd hh24:mi:ss',
status "substr(:status,1,5)"
)
执行导入
D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10
查看日志:
ldr_object.log
经过时间为: 00: 01: 47.00
CPU 时间为: 00: 00: 07.43
step 2: 第二次执行导入
D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10 rows=640
查看日志:
ldr_object.log
经过时间为: 00: 01: 25.66
CPU 时间为: 00: 00: 05.75
setp 3: 第三次执行导入
使用direct参数
D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10 direct=true
查看日志:
ldr_object.log
经过时间为: 00: 00: 31.95
CPU 时间为: 00: 00: 03.92
setp 4: 执行第四次导入
加大流存储区,加大日期格式缓冲区
D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10 direct=true streamsize=10485760 date_cache=5000
查看日志:
ldr_object.log
经过时间为: 00: 00: 15.49
CPU 时间为: 00: 00: 03.26
其他参数比较
1. 调整bindsize参数值默认为256K,修改为10M, 同时将一次加载的行数提高到5000
D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10 bindsize=10485760 rows=5000
查看日志:
ldr_object.log
经过时间为: 00: 00: 55.58
CPU 时间为: 00: 00: 05.19
2. 去掉索引
-- Drop indexes
drop index IDX_OBJ_OWNER_NAME;
加大流存储区,加大日期格式缓冲区
D:\oracle\script>sqlldr scott/tigger control=ldr_object.ctl errors=10 direct=true streamsize=10485760 date_cache=5000
查看日志:
ldr_object.log
经过时间为: 00: 00: 25.45
CPU 时间为: 00: 00: 03.34