接上一篇:
Statistics 统计信息
----------------------------------------------------------
197 recursive calls
185 db block gets
92 consistent gets
60 physical reads
37128 redo size 37128 redo量
664 bytes sent via SQL*Net to client
571 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
10340 rows processed
LS@LEO> rollback; 回滚
Rollback complete.
LS@LEO> insert /*+ append */ into leo_t5 select * from leo_t6; 直接加载
10340 rows created.
Statistics
----------------------------------------------------------
111 recursive calls
180 db block gets
79 consistent gets
21 physical reads
36640 redo size 36640 redo量
664 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10340 rows processed
小结:我们看到传统加载和直接加载产生的redo量并没有太大的差异,因为只要底层数据块发生变化,就会生成redo信息,
不管传统和直接都会修改数据块,用来恢复依据,所以并没有太大的差异。
(10)直接加载和索引
LS@LEO> set autotrace trace stat;
LS@LEO> insert /*+ append */ into leo_t5 select * from leo_t6; 直接加载,但表上没有索引
10340 rows created.
Statistics 统计信息
----------------------------------------------------------
111 recursive calls
175 db block gets
81 consistent gets
15 physical reads
36816 redo size 36816 redo量
664 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10340 rows processed
LS@LEO> create index leo_t5_index on leo_t5(object_id); 给表创建索引
Index created.
LS@LEO> rollback; 回滚
Rollback complete.
LS@LEO> insert /*+ append */ into leo_t5 select * from leo_t6; 直接加载,但表上有索引
10340 rows created.
Statistics 统计信息
----------------------------------------------------------
120 recursive calls
193 db block gets
85 consistent gets
22 physical reads
37344 redo size 37344 redo量
664 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
10340 rows processed
小结:因为有了索引,直接加载redo量比没有索引时有一定的提升,可能是我的测试数据少所以这种提升并不明显,如果在
实际生产库上发生了大量的redo,建议先将索引drop,加载数据后,在重建rebuild索引
(11)直接加载和并行
直接加载和并行是可以一起使用的,以此大幅度提高sql执行效率
LS@LEO> alter session enable parallel dml; 设置会话并行度
Session altered.
LS@LEO> alter session set events '10046 trace name context forever,level 12'; 使用trace文件跟踪sql性能指标
Session altered.
LS@LEO> insert /*+ append parallel(leo_t5,2) */ into leo_t5 select * from leo_t6; 直接加载+并行插入
10340 rows created.
LS@LEO> rollback;
Rollback complete.
LS@LEO> insert /*+ parallel(leo_t5,2) */ into leo_t5 select * from leo_t6; 并行插入
10340 rows created.
LS@LEO> rollback;
Rollback complete.
LS@LEO> insert /*+ append */ into leo_t5 select * from leo_t6; 直接加载
10340 rows created.
LS@LEO> rollback;
Rollback complete.
LS@LEO> insert into leo_t5 select * from leo_t6; 什么特性也没有用
10340 rows created.
LS@LEO> commit; 提交
Commit complete.
[oracle@secdb1 udump]$ tkprof leo_ora_20558.trc leo.txt sys=no 格式化trace文件
TKPROF: Release 10.2.0.1.0 - Production on Sun Aug 5 22:13:38 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
insert /*+ append parallel(leo_t5,2) */ into leo_t5 select * from leo_t6
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 1 0 0
Execute 1 0.03 2.51 8 46 67 10340
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.04 2.53 8 47 67 10340
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
2 PX COORDINATOR (cr=46 pr=0 pw=0 time=2201632 us)
0 PX SEND QC (RANDOM) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
0 LOAD AS SELECT (cr=0 pr=0 pw=0 time=0 us)
0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND ROUND-ROBIN :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
10340 TABLE ACCESS FULL LEO_T6 (cr=42 pr=0 pw=0 time=1356361 us)
insert /*+ parallel(leo_t5,2) */ into leo_t5 select * from leo_t6
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 1 0 0
Execute 1 0.02 1.66 7 44 64 10340
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 1.67 7 45 64 10340
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
2 PX COORDINATOR (cr=44 pr=0 pw=0 time=1209712 us)
0 PX SEND QC (RANDOM) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
0 LOAD AS SELECT (cr=0 pr=0 pw=0 time=0 us)
0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND ROUND-ROBIN :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
10340 TABLE ACCESS FULL LEO_T6 (cr=42 pr=0 pw=0 time=186185 us)
insert /*+ append */ into leo_t5 select * from leo_t6
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.06 0.24 62 113 373 10340
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.06 0.24 62 114 373 10340
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
1 LOAD AS SELECT (cr=113 pr=62 pw=39 time=241775 us)
10340 TABLE ACCESS FULL LEO_T6 (cr=42 pr=0 pw=0 time=62104 us) 没有使用并行操作
insert into leo_t5 select * from leo_t6
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 43 0 0
Execute 1 0.14 0.54 100 101 1022 10340
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total , 2 0.15 0.55 100 144 1022 10340
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
10340 TABLE ACCESS FULL LEO_T6 (cr=42 pr=0 pw=0 time=744520 us) 只有全表扫描
小结:insert /*+ append parallel(leo_t5,2) */ into leo_t5 select * from leo_t6和insert /*+ parallel(leo_t5,2)
*/ into leo_t5 select * from leo_t6执行计划是一样的,因为当使用parallel并行插入时,oracle默认使用直接加载方式
来加载数据,因此append关键字可忽略了。
注:如果执行alter session disable parallel dml; oracle就会禁用DML并行操作,就算有hint提示也不会起作用,那么
insert /*+ append parallel(leo_t5,2) */和insert /*+ append */的执行计划都应该是一样的了,都是只有直接加载,没
有并行效果了
(12)直接加载和sqlload
sqlload 是我们常用的文本加载工具,它可以把文本文件按照一定的格式批量加载到数据库中去,现在我们测试传统加载
conventional、直接加载direct、并行parallel直接加载的性能对比和执行效率。
-rwxrwxrwx 1 oracle oinstall 283 Aug 9 00:11 leo_test.ctl 控制文件
-rwxrwxrwx 1 oracle oinstall 8983596 Aug 8 20:57 leo_test.data 数据文件,10万行数据,9个字段
-rwxrwxrwx 1 oracle oinstall 2099 Aug 9 00:15 leo_test.log 日志文件
1.传统加载conventional 10万行记录->表LEO_TEST_SQLLOAD
sqlldr userid=ls/ls control=leo_test.ctl 传统加载数据
LS@LEO> select count(*) from leo_test_sqlload;
COUNT(*)
----------
100000
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 9 00:14:15 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: leo_test.ctl 控制文件
Data File: /home/oracle/leo_test.data 数据文件
Bad File: leo_test.bad 坏文件
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional sqlload采用传统加载方式,数据要通过缓冲区加载到表中
Table LEO_TEST_SQLLOAD, loaded from every logical record.
Insert option in effect for this table: APPEND 采用追加的方式加载,新数据不覆盖旧数据,而是结尾累加
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype 列信息
------------------------------ ---------- ----- ---- ---- ---------------------
START_TIME FIRST * | DATE YYYY-MM-DD HH24:MI:SS
END_TIME NEXT * | DATE YYYY-MM-DD HH24:MI:SS
PROTOCOL NEXT * | CHARACTER
PRIVATE_IP NEXT * | CHARACTER
PRIVATE_PORT NEXT * | CHARACTER
SRC_IP NEXT * | CHARACTER
SRC_PORT NEXT * | CHARACTER
DEST_IP NEXT * | CHARACTER
DEST_PORT NEXT * | CHARACTER
Table LEO_TEST_SQLLOAD:
100000 Rows successfully loaded. 10万行记录成功加载
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: 148608 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 100000
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Thu Aug 09 00:14:15 2012
Run ended on Thu Aug 09 00:15:21 2012
Elapsed time was: 00:01:05.60 耗时65秒
CPU time was: 00:00:00.81
2.直接加载direct 10万行记录->表LEO_TEST_SQLLOAD1
LS@LEO> select df.tablespace_name "表空间名",totalspace "总空间M",freespace "剩余空间M",round((1-
freespace/totalspace)*100,2) "使用率%"
from (select tablespace_name,round(sum(bytes)/1024/1024) totalspace from dba_data_files group by
tablespace_name) df,
(select tablespace_name,round(sum(bytes)/1024/1024) freespace from dba_free_space group by tablespace_name)
fs
where df.tablespace_name=fs.tablespace_name order by df.tablespace_name ; 2 3 4 5
表空间名 总空间M 剩余空间M 使用率%
------------------------------ ---------- ------------- ----------
CTXSYS 32 27 15.63
EXAMPLE 200 199 .5
SYSAUX 325 266 18.15
SYSTEM 325 84 74.15
UNDOTBS 200 189 5.5
USERS 600 501 16.5 没有加载表leo_test_sqlload1之前空间情况
sqlldr userid=ls/ls control=leo_test1.ctl data=leo_test.data log=leo_test1.log direct=true 直接加载10万行
数据
LS@LEO> select count(*) from leo_test_sqlload1;
COUNT(*)
----------
100000 (3M)
LS@LEO> select df.tablespace_name "表空间名",totalspace "总空间M",freespace "剩余空间M",round((1-
freespace/totalspace)*100,2) "使用
率%"
from
(select tablespace_name,round(sum(bytes)/1024/1024) totalspace from dba_data_files group by tablespace_name)
df,
(select tablespace_name,round(sum(bytes)/1024/1024) freespace from dba_free_space group by tablespace_name)
fs
where df.tablespace_name=fs.tablespace_name order by df.tablespace_name ; 2 3 4 5
表空间名 总空间M 剩余空间M 使用率%
------------------------------ ---------- ------------- ----------
CTXSYS 32 27 15.63
EXAMPLE 200 199 .5
SYSAUX 325 266 18.15
SYSTEM 325 84 74.15
UNDOTBS 200 189 5.5
USERS 600 498 17 10万行记录加载后使用了3M空间
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 9 01:07:52 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: leo_test1.ctl 控制文件
Data File: leo_test.data 数据文件,10万行数据,9个字段
Bad File: leo_test.bad 坏文件
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct sqlload采用直接加载方式,数据不通过缓冲区和sql语法引擎直接加载到表中
Table LEO_TEST_SQLLOAD1, loaded from every logical record.
Insert option in effect for this table: APPEND 采用追加的方式加载,新数据不覆盖旧数据,而是结尾累加
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype 列信息
------------------------------ ---------- ----- ---- ---- ---------------------
START_TIME FIRST * | DATE YYYY-MM-DD HH24:MI:SS
END_TIME NEXT * | DATE YYYY-MM-DD HH24:MI:SS
PROTOCOL NEXT * | CHARACTER
PRIVATE_IP NEXT * | CHARACTER
PRIVATE_PORT NEXT * | CHARACTER
SRC_IP NEXT * | CHARACTER
SRC_PORT NEXT * | CHARACTER
DEST_IP NEXT * | CHARACTER
DEST_PORT NEXT * | CHARACTER
Table LEO_TEST_SQLLOAD1:
100000 Rows successfully loaded. 10万行记录成功加载,占用3M磁盘空间
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.
Date cache:
Max Size: 1000
Entries : 65
Hits : 199935
Misses : 0
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: 100000
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 26
Total stream buffers loaded by SQL*Loader load thread: 17
Run began on Thu Aug 09 01:07:52 2012
Run ended on Thu Aug 09 01:07:56 2012
Elapsed time was: 00:00:03.53 耗时3秒.53 比 传统加载65秒节约了94%时间
CPU time was: 00:00:00.25
小结:因此我们知道直接加载要比传统加载执行效率高很多,当我们的系统负载不高,资源充裕时可以考虑使用直接加载
direct方式批量导入数据,即减少了I/O和内存开销,又提高了数据加载效率。
3.并行直接加载direct 10万行记录->表LEO_TEST_SQLLOAD2
表空间名 总空间M 剩余空间M 使用率%
------------------------------ ---------- ------------- ----------
USERS 600 498 17 没有加载前表空间的空间状态
sqlldr userid=ls/ls control=leo_test2.ctl data=leo_test.data log=leo_test2.log direct=true parallel=true
并行直接加载10万行数据
LS@LEO> select count(*) from leo_test_sqlload2;
COUNT(*)
----------
100000 (8M)
表空间名 总空间M 剩余空间M 使用率%
------------------------------ ---------- ------------- ----------
USERS 600 490 18.33 10万行记录加载后使用了8M空间
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 9 07:25:00 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: leo_test2.ctl 控制文件
Data File: leo_test.data 数据文件,10万行数据,9个字段
Bad File: leo_test.bad 坏文件
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct - with parallel option. sqlload采用并行+直接加载方式,既有并行,又有直接加载,双重
功效,效率更高
Table LEO_TEST_SQLLOAD2, loaded from every logical record.
Insert option in effect for this table: APPEND 采用追加的方式加载,新数据不覆盖旧数据,而是结尾累加
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype 列信息
------------------------------ ---------- ----- ---- ---- ---------------------
START_TIME FIRST * | DATE YYYY-MM-DD HH24:MI:SS
END_TIME NEXT * | DATE YYYY-MM-DD HH24:MI:SS
PROTOCOL NEXT * | CHARACTER
PRIVATE_IP NEXT * | CHARACTER
PRIVATE_PORT NEXT * | CHARACTER
SRC_IP NEXT * | CHARACTER
SRC_PORT NEXT * | CHARACTER
DEST_IP NEXT * | CHARACTER
DEST_PORT NEXT * | CHARACTER
Table LEO_TEST_SQLLOAD2:
100000 Rows successfully loaded. 10万行记录成功加载,占用8M磁盘空间
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.
Date cache:
Max Size: 1000
Entries : 65
Hits : 199935
Misses : 0
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: 100000
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 26
Total stream buffers loaded by SQL*Loader load thread: 17
Run began on Thu Aug 09 07:25:00 2012
Run ended on Thu Aug 09 07:25:13 2012
Elapsed time was: 00:00:12.77 耗时00:00:12.77 比 直接加载3秒.53节约了93%时间
CPU time was: 00:00:00.98
小结:从时间成本上我们就可看出,并行直接加载效率要远远的高出串行直接加载,在海量数据的环境中使用并行和直接加
载的技术,对提高效率和性能那是如虎添翼(并行并不一定比串行好,主要看业务类型其次看资源情况),我们应该思考“
理解技术如何为业务服务”,这要比单纯学技术更加重要,谢谢!!!
(12)sqlload直接加载对索引的影响
所谓对索引的影响是指使用sqlload加载存在索引的表的数据时索引是否有效
非约束索引:sqlload直接加载完毕后维护索引的完整性,此时索引不失效
约束索引:例如 主键 外键 唯一索引 sqlload直接加载完毕后,数据会入库但索引会失效unusable,此时要重建索引
1.非约束索引,直接加载完毕后维护索引的完整性,此时索引不失效
LS@LEO> select count(*) from leo_test_sqlload1; 表中有10条记录
COUNT(*)
----------
100000
LS@LEO> create index leo_test_sqlload1_index on leo_test_sqlload1(private_ip); 在private_ip上创建B-tree索引
Index created.
LS@LEO> select status from user_indexes where table_name='LEO_TEST_SQLLOAD1'; 检查索引的有效性valid
STATUS
--------
VALID
sqlldr userid=ls/ls control=leo_test1.ctl data=leo_test.data log=leo_test1.log direct=true 直接加载后会维护
索引的完整性
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 9 15:27:03 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 100000. 10万行记录成功加载
LS@LEO> select count(*) from leo_test_sqlload1; 现在表中有20万条记录
COUNT(*)
----------
200000
LS@LEO> select status from user_indexes where table_name='LEO_TEST_SQLLOAD1'; 自动维护索引的有效性vaild,
对非约束索引而言
STATUS
--------
VALID
2.约束索引:例如 主键 外键 唯一索引 sqlload直接加载完毕后,数据会入库但索引会失效unusable,此时要重建索引
LS@LEO> create table leo_test_sqlload3
(
START_TIME date,
END_TIME date,
PROTOCOL varchar(20),
PRIVATE_IP varchar(20),
PRIVATE_PORT varchar(20) constraint pk_leo_test_sqlload3 primary key , 我们创建一个带主键的表
SRC_IP varchar(20),
SRC_PORT varchar(20),
DEST_IP varchar(20),
DEST_PORT varchar(20)
);
Table created.
LS@LEO> select * from leo_test_sqlload3; 现在表中没有数据
no rows selected
sqlldr userid=ls/ls control=leo_test3.ctl data=leo_test1.data log=leo_test3.log direct=true
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 9 15:49:10 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 100. 100行记录成功加载
LS@LEO> select * from leo_test_sqlload3; 数据已加载,但PRIVATE_PORT主键索引已经失效,因为我
们的值全是一样的
START_TIME END_TIME PR PRIVATE_IP PRIV SRC_IP SRC_PORT DEST_IP DEST
---------------------- ---------------------- -- ------------ ---- ------------ -------- ------------ ----
2012-08-08 20:59:54 2012-08-08 21:00:28 6 2886756061 1111 3395517721 45031 3419418065 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886900807 1111 3395507143 51733 3658060738 80
2012-08-08 20:59:55 2012-08-08 21:00:28 6 2886917742 1111 3395507159 43516 2071873572 80
2012-08-08 20:59:55 2012-08-08 21:00:28 6 2886917742 1111 3395507159 43534 2071873572 80
2012-08-08 20:59:55 2012-08-08 21:00:28 6 2886917742 1111 3395507159 43523 2071873572 80
2012-08-08 21:00:14 2012-08-08 21:00:28 6 2886832065 1111 3395507109 51442 2099718013 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886794376 1111 3395507104 57741 2071819251 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886758392 1111 3395517723 56875 1007173560 80
2012-08-08 21:00:22 2012-08-08 21:00:28 6 2886862137 1111 3395517760 17744 3626142915 7275
2012-08-08 21:00:25 2012-08-08 21:00:28 6 2886741689 1111 3395517708 14954 2007469330 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886891044 1111 3395517787 23626 1872834975 443
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886790049 1111 3395507100 54215 1884995806 80
2012-08-08 21:00:15 2012-08-08 21:00:28 6 2886771544 1111 3395507083 32261 1872832004 80
2012-08-08 21:00:24 2012-08-08 21:00:28 6 2886796616 1111 3395517729 18634 2007467546 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886839912 1111 3395507117 10102 1850510469 5242
2012-08-08 21:00:23 2012-08-08 21:00:28 6 2886742978 1111 3395517709 28276 1021181676 80
2012-08-08 21:00:16 2012-08-08 21:00:28 6 2886792600 1111 3395507103 15204 974546887 80
2012-08-08 21:00:23 2012-08-08 21:00:28 6 2886890096 1111 3395517786 30741 1884983225 80
2012-08-08 21:00:00 2012-08-08 21:00:28 6 2886743885 1111 3395517710 18678 1884968358 80
2012-08-08 21:00:16 2012-08-08 21:00:28 6 2886792600 1111 3395507103 15237 974547338 80
2012-08-08 21:00:10 2012-08-08 21:00:28 6 2886828509 1111 3395507106 30179 2007493616 80
2012-08-08 21:00:25 2012-08-08 21:00:28 6 2886811814 1111 3395517743 34249 2072702869 80
2012-08-08 20:59:57 2012-08-08 21:00:28 6 2886780595 1111 3395507091 63169 1872834775 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886745283 1111 3395517711 38566 1863134645 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886852868 1111 3395507129 19216 989566331 80
2012-08-08 21:00:22 2012-08-08 21:00:28 6 2886758076 1111 3395517723 37910 3061190502 80
2012-08-08 21:00:22 2012-08-08 21:00:28 6 2886758076 1111 3395517723 37886 2079006794 80
2012-08-08 21:00:25 2012-08-08 21:00:28 6 2886788330 1111 3395507099 15078 460553383 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886756269 1111 3395517721 57538 2008813541 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886906371 1111 3395507148 65509 1884961048 80
2012-08-08 20:59:51 2012-08-08 21:00:28 6 2886893244 1111 3395517789 27585 2071802397 995
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886810351 1111 3395517742 10465 1971814472 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886908390 1111 3395507150 58599 3419418057 80
2012-08-08 21:00:11 2012-08-08 21:00:28 6 2886811967 1111 3395517743 43433 2099759129 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886908416 1111 3395507150 60161 1027056891 80
2012-08-08 21:00:24 2012-08-08 21:00:28 6 2886794472 1111 3395507104 63499 1872769542 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886859643 1111 3395507135 41589 1008470934 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886908926 1111 3395507151 26758 1027061456 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886844821 1111 3395507121 48598 989542829 80
2012-08-08 21:00:14 2012-08-08 21:00:28 6 2886811914 1111 3395517743 40207 2071819051 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886776231 1111 3395507087 57398 1027061476 80
2012-08-08 21:00:21 2012-08-08 21:00:28 6 2886895128 1111 3395507138 31084 1020918811 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886896369 1111 3395507139 41560 2071819499 80
2012-08-08 21:00:15 2012-08-08 21:00:28 6 2886866997 1111 3395517764 53220 1008528500 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886733364 1111 3395517700 27617 1850417510 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886763900 1111 3395507076 21749 2072679568 80
2012-08-08 21:00:24 2012-08-08 21:00:28 6 2886848688 1111 3395507125 24485 460553373 80
2012-08-08 20:59:50 2012-08-08 21:00:28 6 2886866792 1111 3395517764 40930 2072313366 80
2012-08-08 20:59:55 2012-08-08 21:00:28 6 2886917742 1111 3395507159 43536 2071873572 80
2012-08-08 20:59:55 2012-08-08 21:00:28 6 2886917742 1111 3395507159 43542 2071873572 80
2012-08-08 20:59:53 2012-08-08 21:00:28 6 2886801934 1111 3395517734 17623 2007483189 8080
2012-08-08 20:59:55 2012-08-08 21:00:28 6 2886917742 1111 3395507159 43537 2071873572 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886886283 1111 3395517782 58048 2071816694 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886735314 1111 3395517702 16591 2071799544 80
2012-08-08 20:59:55 2012-08-08 21:00:28 6 2886917742 1111 3395507159 43524 2071873572 80
2012-08-08 21:00:20 2012-08-08 21:00:28 6 2886849684 1111 3395507126 20262 2008825959 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886872604 1111 3395517770 5537 3419418056 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886853794 1111 3395507130 10753 2099722272 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886755008 1111 3395517720 45872 1883357744 80
2012-08-08 21:00:21 2012-08-08 21:00:28 6 2886895128 1111 3395507138 31121 2078933535 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886864839 1111 3395517762 51804 1850417452 80
2012-08-08 21:00:19 2012-08-08 21:00:28 6 2886858061 1111 3395507134 10700 2071819372 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886776231 1111 3395507087 57410 1027061476 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886858854 1111 3395507134 58306 1020914578 80
2012-08-08 21:00:21 2012-08-08 21:00:28 6 2886774805 1111 3395507086 35831 1883303354 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886794557 1111 3395507105 4593 3708103499 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886747135 1111 3395517713 21641 2099740446 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886863802 1111 3395517761 53630 1863145458 5224
2012-08-08 21:00:22 2012-08-08 21:00:28 6 2886911235 1111 3395507153 37254 2095615735 21
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886860043 1111 3395507136 1581 294986889 5223
2012-08-08 20:59:56 2012-08-08 21:00:28 6 2886780595 1111 3395507091 63161 1883302610 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886732547 1111 3395517699 42653 294986856 5223
2012-08-08 20:59:54 2012-08-08 21:00:28 6 2886734208 1111 3395517701 14230 2007484922 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886866964 1111 3395517764 51273 2072105082 80
2012-08-08 21:00:00 2012-08-08 21:00:28 6 2886780595 1111 3395507091 63144 1872834775 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886914262 1111 3395507156 26777 2072104968 80
2012-08-08 20:59:54 2012-08-08 21:00:28 6 2886734208 1111 3395517701 14273 2007484922 80
2012-08-08 21:00:25 2012-08-08 21:00:28 6 2886847997 1111 3395507124 47084 2021394494 80
2012-08-08 21:00:21 2012-08-08 21:00:28 6 2886785128 1111 3395507096 15002 294986849 5223
2012-08-08 21:00:25 2012-08-08 21:00:28 6 2886783177 1111 3395507094 26001 2072101596 443
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886735924 1111 3395517702 53178 1850417918 80
2012-08-08 21:00:09 2012-08-08 21:00:28 6 2886837532 1111 3395507114 59353 2071819198 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886891515 1111 3395517787 51880 1884983223 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886737305 1111 3395517704 8009 1872834975 443
2012-08-08 21:00:16 2012-08-08 21:00:28 6 2886755910 1111 3395517721 35947 2918544417 80
2012-08-08 21:00:27 2012-08-08 21:00:28 6 2886771117 1111 3395507083 6645 1884960474 80
2012-08-08 21:00:20 2012-08-08 21:00:28 6 2886785801 1111 3395507096 55430 2099718013 80
2012-08-08 21:00:24 2012-08-08 21:00:28 6 2886756061 1111 3395517721 45056 3419418065 80
2012-08-08 21:00:14 2012-08-08 21:00:28 6 2886771706 1111 3395507083 41990 1883302599 80
2012-08-08 20:59:55 2012-08-08 21:00:28 6 2886917742 1111 3395507159 43511 2071873572 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886853131 1111 3395507129 34983 296567345 443
2012-08-08 20:59:55 2012-08-08 21:00:28 6 2886917742 1111 3395507159 43538 2071873572 80
2012-08-08 21:00:23 2012-08-08 21:00:28 6 2886857519 1111 3395507133 42212 460553373 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886886465 1111 3395517783 4972 989566680 80
2012-08-08 21:00:25 2012-08-08 21:00:28 6 2886753976 1111 3395517719 47964 1884981528 80
2012-08-08 20:59:56 2012-08-08 21:00:28 6 2886809185 1111 3395517741 4537 2071872692 80
2012-08-08 21:00:26 2012-08-08 21:00:28 6 2886840353 1111 3395507117 36547 1027051331 80
2012-08-08 21:00:20 2012-08-08 21:00:28 6 2886840637 1111 3395507117 53634 1872832059 80
2012-08-08 21:00:19 2012-08-08 21:00:28 6 2886876032 1111 3395517773 19163 1884968518 80
2012-08-08 21:00:19 2012-08-08 21:00:28 6 2886876032 1111 3395517773 19158 1884968518 80
100 rows selected.
LS@LEO> select index_name,index_type,status from user_indexes where table_name='LEO_TEST_SQLLOAD3'; 我们创建
主键已经失效
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
PK_LEO_TEST_SQLLOAD3 &nbs,p; NORMAL UNUSABLE
3.sqlload并行+直接加载存在索引的表,此时加载会失败,skip_index_maintenance=true参数可以跳过索引维护完成加载,
此时索引状态unusable需要手工重建rebuild
create table leo_test_sqlload4 定义一个有主键的表
(
START_TIME date,
END_TIME date,
PROTOCOL varchar(20),
PRIVATE_IP varchar(20),
PRIVATE_PORT varchar(20) constraint pk_leo_test_sqlload4 primary key ,
SRC_IP varchar(20),
SRC_PORT varchar(20),
DEST_IP varchar(20),
DEST_PORT varchar(20)
);
sqlldr userid=ls/ls control=leo_test4.ctl data=leo_test1.data log=leo_test4.log direct=true parallel=true
并行+直接加载
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 9 16:19:25 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Loader-951: Error calling once/load initialization 报错:加载初始化参数错误
ORA-26002: Table LS.LEO_TEST_SQLLOAD4 has index defined upon it. 表上有索引定义,所以加载会失败
LS@LEO> select index_name,index_type,status from user_indexes where table_name='LEO_TEST_SQLLOAD4';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
PK_LEO_TEST_SQLLOAD4 NORMAL VALID 现在索引还是有效的
sqlldr userid=ls/ls control=leo_test4.ctl data=leo_test1.data log=leo_test4.log direct=true parallel=true
skip_index_maintenance=true;
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 9 16:30:52 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 100. 使用skip_index_maintenance=true跳过索引维护,100行记录成功加载
LS@LEO> select count(*) from leo_test_sqlload4;
COUNT(*)
----------
100
LS@LEO> select index_name,index_type,status from user_indexes where table_name='LEO_TEST_SQLLOAD4';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
PK_LEO_TEST_SQLLOAD4 NORMAL UNUSABLE 加载后索引状态变成unusable需要手工重建
rebuild
小结:我们在sqlload工具加载数据时一定要关注表上是否有索引,并且是什么类型的,正像世界万物一样,没有完美的工具
,有得必有失,如果提高性能就会索引失效,如果要维护索引的完整性那么就会增加性能开销,我们要做的更加细心、严谨
、谦虚,以不变应万变。
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html