【Direct-Path】直接路径加载提升插入效率及其自身限制

在加载大量数据的时候，可以考虑使用直接路径加载技术来加速。不过该技术本身也存在一些弊端，在使用前需要权衡利弊。本文将通过实验介绍该技术的 一些 点滴细节，供大家参考。

1.初始化实验用表
共两张表。 表t 包含1千万条记录；另外一张 表t_insert 用于演示常规插入和直接路径插入，t_insert表是使用t表反复插入删除后构造的一个不包含记录但具有非常高的高水位线的表。
1）经确认，表t_insert的段大小是6.4G。
sec@ora10g> select owner,
  2         segment_name table_name,
  3         segment_type TYPE,
  4         bytes / 1024 / 1024 MB
  5    from dba_segments
  6   where segment_type like 'TABLE%'
  7     and segment_name = 'T_INSERT'
  8  /

OWNER         TABLE_NAME     TYPE               MB
------------- -------------- ---------- ----------
SEC           T_INSERT       TABLE            6410

2）确认表t_insert不包含任何记录
sec@ora10g> select count(*) from t_insert;

  COUNT(*)
----------
         0

Elapsed: 00:00:05.85

2.使用常规插入方法
1）常规插入方法共用时1分16秒，时间较长。
sec@ora10g> set timing on
sec@ora10g> set autot on
sec@ora10g> insert into t_insert select * from t;

10000000 rows created.

Elapsed: 00:01:16.66

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | INSERT STATEMENT  |      |  9990K|   876M| 30060   (2)| 00:06:01 |
|   1 |  TABLE ACCESS FULL| T    |  9990K|   876M| 30060   (2)| 00:06:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         10  recursive calls
    1011406  db block gets
     403769  consistent gets
     136143  physical reads
 1096000288  redo size
       1129  bytes sent via SQL*Net to client
       1189  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
   10000000  rows processed

2）尝试回滚
注意，如果此时进行回滚，回滚操作是比较耗时的。因为这种常规插入方式的回滚是要完成真正的回滚（使用undo表空间中记录的信息删除之前插入的记录）还有回滚资源的释放等维护操作。
sec@ora10g> rollback;

Rollback complete.

Elapsed: 00:00:04.29

回滚动作历时约4.5秒。

3）常规插入方法因为是在高水位线之下完成的，因此表的段空间没有变大。
sec@ora10g> select owner,
  2         segment_name table_name,
  3         segment_type TYPE,
  4         bytes / 1024 / 1024 MB
  5    from dba_segments
  6   where segment_type like 'TABLE%'
  7     and segment_name = 'T_INSERT'
  8  /

OWNER         TABLE_NAME     TYPE               MB
------------- -------------- ---------- ----------
SEC           T_INSERT       TABLE            6410

3.再来看一下使用直接路径加载方式完成插入的情况
1）直接路径加载方法供用时18秒，与之前的常规插入相比，大大加快了插入速度
sec@ora10g> set autot on
sec@ora10g> insert /*+ append */ into t_insert select * from t;

10000000 rows created.

Elapsed: 00:00:18.16

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
          0  recursive calls
     135571  db block gets
     135436  consistent gets
     135368  physical reads
 1114462952  redo size
       1113  bytes sent via SQL*Net to client
       1203  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
   10000000  rows processed

2）尝试回滚
注意，如果此时尝试事务回滚，回滚操作是非常快速的，因为这里不涉及真正的回滚操作。
sec@ora10g> rollback;

Rollback complete.

Elapsed: 00:00:00.00

3）上面在执行计划获取时为什么会报“ORA-12838”错误呢？
因为事务提交前表t_insert的内容是不允许被读取的。在执行直接路径加载的过程中，高水位并没有真正提高，只有在事务提交后才会完成这个动作，在所有维护工作完成之后表才可以被访问。所以，在提交之前如果想查询这张表是不被允许的，同理可知对表 t_insert的 增删改以及merge操作也是不被允许的。
验证查询不被允许：
sec@ora10g> select count(*) from t_insert;
select count(*) from t_insert
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

验证插入不被允许：
sec@ora10g> insert into t_insert select * from t where rownum<2;
insert into t_insert select * from t where rownum<2
            *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

验证删除不被允许：
sec@ora10g> delete from t_insert where rownum<2;
delete from t_insert where rownum<2
            *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

验证修改不被允许：
sec@ora10g> update t_insert set x = 1 where rownum<2;
update t_insert set x = 1 where rownum<2
       *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

4）我们完成事务提交，此时查询结果便可成功返回。
sec@ora10g> commit;

Commit complete.

sec@ora10g> select count(*) from t_insert;

  COUNT(*)
----------
  10000000

Elapsed: 00:00:07.11

5）直接路径加载对段空间的影响
因为直接路径加载是在高水位之上完成的插入动作，因此无论高水位下有多少空闲块都会被忽略，段空间将会随之增大。
sec@ora10g> select OWNER,
  2         segment_name table_name,
  3         segment_type TYPE,
  4         bytes / 1024 / 1024 MB
  5    from dba_segments
  6   where segment_type like 'TABLE%'
  7     and segment_name = 'T_INSERT'
  8  /

OWNER         TABLE_NAME     TYPE               MB
------------- -------------- ---------- ----------
SEC           T_INSERT       TABLE            7434

7434MB相比之前的 6410MB增加了近1G的大小。

4.直接路径加载的一些限制
使用直接路径加载方法时需要注意的地方如下：
1）直接路径加载方法不是所有插入方式都支持的，最常见的带有value子句的insert语句就不支持；
2）该技术最常用在insert into ... select ...结构的插入语句中；
3）在使用直接路径加载技术插入数据直到事务提交，其他的增、删、改、查和merge操作是被禁止的；
4）因为是直接路径加载，所以高水位以下的空闲数据库块将不被使用，可能会因此导致数据段无限扩张；
5）当被操作的表上存在insert触发器、外键、表类型是IOT、表使用到了聚簇技术以及表中包含LOB字段时，直接路径加载技术是无效的，此时将会自动的转变为常规插入。

5.小结
使用直接路径加载技术之所以能提高性能是因为，该方法可以保证在加载数据的过程中最大限度的减少回滚数据的生成。
虽然使用直接路径加载技术存在很多限制，不过在权衡利弊后如果能正确使用该方法，提高数据加载的效率是肯定的，如若系统允许以nologging方式完成加载，效果更佳。

Good luck.

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-620246/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-620246/