oracle 并行原理深入解析及案例精粹2

最新推荐文章于 2021-04-12 23:04:29 发布

weixin_33826609

最新推荐文章于 2021-04-12 23:04:29 发布

阅读量96

点赞数

文章标签： shell 数据库

原文链接：http://blog.51cto.com/19880614/1282896

版权

接上一篇：

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

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

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)

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)

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

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

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

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

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

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

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

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

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

转载于:https://blog.51cto.com/19880614/1282896

weixin_33826609

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 并行原理深入解析及案例精粹2

接上一篇：Statistics 统计信息---------------------------------------------------------- 197 recursive calls 185 db block gets 92 consistent gets 60 physical reads...
复制链接

扫一扫