Oracle直接路径加载--append的深度解析

转自：http://www.linuxidc.com/Linux/2013-02/79108.htm

 ㈠ 直接路径加载和buffer cache

直接路径插入的数据不经过buffer cache，从PGA直接把数据格式化成Oracle块

然后由普通的Oracle Server Process进程把数据块写入数据文件

因为不经过buffer cache，所以不需要DBWn介入

假如有表a，现要将a中的数据插入表b，在普通的插入下，需先将a的数据块I/O到buffer cache

在buffer cache中从a的块中读出行，插进b的块中

此时，b的块就都变成了脏块，再等待DBWn把他们flush到数据文件

因此，普通插入后，a表和b表的块都会在buffer cache中出现

而直接路径插入，将a表的数据块I/O到buffer cache，读出行，直接写进b表所在的数据文件

插入完成后，除了表头块外，b表的数据块并未在buffer cache中出现过

测试：

hr@ORCL> create table a (id number,name varchar2(10));

Table created.

hr@ORCL> create table b (id number,name varchar2(10));

Table created.

hr@ORCL> insert into a values(1,'aa');

1 row created.

hr@ORCL> insert into a values(2,'bb');

1 row created.

hr@ORCL> insert into a values(3,'cc');

1 row created.

hr@ORCL> insert into a values(4,'dd');

1 row created.

hr@ORCL> commit;

Commit complete.

hr@ORCL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from a;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                  4                                  508
                                  4                                  508
                                  4                                  508
                                  4                                  508
--现在a表有4行，占用块508，而目前b表还木有数据
  
--将buffer cache清空

hr@ORCL> alter system flush buffer_cache;

System altered.

--先用直接路径插入，从a表向b表插入数据

hr@ORCL> insert /*+ append */ into b select * from a;

4 rows created.

hr@ORCL> commit;

Commit complete.

--使用v$bh查看buffer cache中的块

hr@ORCL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='A');

    FILE#    BLOCK#
---------- ----------
        4        508    ←←当前包含数据的块
        4        508    ←←当前包含数据的块
        4        511
        4        511
        4        506
        4        509
        4        509
        4        512
        4        512
        4        507
        4        507
        4        510
        4        510
        4        505

14 rows selected.

--这是因为对a表进行全表扫，a表中低高水位点下所有的块都被读进buffer cache，这其中当然也包括508了

hr@ORCL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='B');

    FILE#    BLOCK#
---------- ----------
        4      2571
        4      2569
        4      2570

hr@ORCL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from b;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                  4                                2572
                                  4                                2572
                                  4                                2572
                                  4                                2572
--上面两个查询可以看到，b表中的数据占用地2572块，但是，直接路径插入后，2572并没有被调入buffer cache
--buffer cache中只有2569 2570 2571
--其中2571是段头块(select header_file,header_block from dba_segments where segment_name='B') 
--2570 2569则是L1 L2这两个位图块
--接下来使用普通插入
hr@ORCL> alter system flush buffer_cache;

System altered.

hr@ORCL> insert into b select * from a;

4 rows created.

hr@ORCL> commit;

Commit complete.

hr@ORCL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='B');

    FILE#    BLOCK#
---------- ----------
        4      2571
        4      2574
        4      2569
        4      2575
        4      2570
        4      2570
        4      2573
        4      2576  ←←本次普通插入的数据所在的块

8 rows selected.

hr@ORCL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from b;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                  4                                2572
                                  4                                2572
                                  4                                2572
                                  4                                2572
                                  4                                2576
                                  4                                2576
                                  4                                2576
                                  4                                2576

8 rows selected.

从上面的实验可以证明，普通插入，要先将数据块传进buffer cache

这是Oracle通常修改数据的方式，不对数据文件直接进行修改，而是在内存中完成修改，再由日志提供保护

对于小量的修改，这是种可取的方法，但对于大数据交易，直接路径将可以提供更好的性能

还有就是，直接路径加载是在高水位之上完成的插入动作，因此无论高水位下有多少空闲块都会被忽略，段空间将会随之增大

 ㈡ 直接路径加载和undo

差别比较明显的是undo(直接路径几乎没有undo)，俩者redo差不多(普通插入稍微多点redo，因为Oracle需要redo来保护undo)

直接路径用HWM回滚，普通插入用undo回滚

--再次向b表使用直接路径插入

hr@ORCL> insert /*+ append */ into b select id+4,name from a;

4 rows created.

--查看事务信息

hr@ORCL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn from v$transaction;

    XIDUSN    XIDSLOT    XIDSQN    UBAFIL    UBABLK    UBASQN
---------- ---------- ---------- ---------- ---------- ----------
        10        23        314          0          0          0
--因为当前只有一个事务，因此选择v$transaction视图时没有加条件
--从上面的显示结果可以看到，ubafil ubablk为0，也就是，此事务没有对应的回滚块
--只有在回滚段头的事务表中占用了一行而已

那么直接路径插入是如何提供回滚的呢？观察b表的HWM的变化，就可以解答这个问题

--查找b表的HWM

hr@ORCL> select header_file,header_block from dba_segments where segment_name='B';

HEADER_FILE HEADER_BLOCK
----------- ------------
          4        2571

hr@ORCL> alter session set tracefile_identifier='hr_2571';

Session altered.

hr@ORCL> alter system dump datafile 4 block 2571;

System altered.

--trc文件摘入如下：

  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 2      #blocks: 16
                  last map  0x00000000  #maps: 0      offset: 2716
      Highwater::  0x01000a11  ext#: 0      blk#: 8      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 8
  mapblk  0x00000000  offset: 0
                  Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x01000a11  ext#: 0      blk#: 8      ext size: 8

--高水位点是4号文件2577号块
--提交后查看直接路径插入到哪个块中

hr@ORCL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from b where id>=8;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                  4                                2577
--通过上面的查询，本次直接路径插入，数据被存进2577号块，而提交的HWM正是2577

|--------------|--------|--------|----------
|数据块 ...... |2575|2576|2577|2578|...
|--------------|--------|--------|----------
                          ↑
                          ↑
                          ↑
                        此处是高水位点，直接路径插入从此块开始分配空间
--直接路径插入，是在高水位点之上分配临时段，将数据插入时进入此临时段
--在提交后将高水位点提升至临时段之上
--现在已经提交，再来看高水位点的信息

hr@ORCL> alter system dump datafile 4 block 2571;

System altered.

--trc文件摘入如下：

  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 2      #blocks: 16
                  last map  0x00000000  #maps: 0      offset: 2716
      Highwater::  0x01000a12  ext#: 1      blk#: 1      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 9
  mapblk  0x00000000  offset: 1
                  Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x01000a12  ext#: 1      blk#: 1      ext size: 8

--第一次dump是Highwater::  0x01000a11，而现在是 Highwater::  0x01000a12
--高水位点升至2578块，如下图

|--------------|--------|--------|--------|-----
|数据块 ......  |2575  |2576|2577|2578|
|--------------|--------|--------|--------|-----                                      
                                  ↑
                                  ↑
                                  ↑
                                高水位点上升至此处

--再试一次直接路径插入回滚时的情况
--先猜想一下，此次插入应该插入到2578，如果提交的话，就提升高水位点到2579，如果回滚的话，保持高水位点不变

hr@ORCL> insert /*+ append */ into b select id+4,name from a;

4 rows created.

hr@ORCL> commit;

Commit complete.

--trc摘入如下：

  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 2      #blocks: 16
                  last map  0x00000000  #maps: 0      offset: 2716
      Highwater::  0x01000a13  ext#: 1      blk#: 2      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 10
  mapblk  0x00000000  offset: 1
      Disk Lock:: Locked by xid:  0x0006.012.0000018d
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x01000a13  ext#: 1      blk#: 2      ext size: 8
--高水位点变为 0x01000a13 ，也就是2579块
--接下来是回滚，回滚则将保持高水位点不变，也就是在2579块

hr@ORCL> insert /*+ append */ into b select id+12,name from a;

4 rows created.

hr@ORCL> rollback;

Rollback complete.

hr@ORCL> alter system dump datafile 4 block 2571;

System altered.

--trc文件摘入如下：

  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 2      #blocks: 16
                  last map  0x00000000  #maps: 0      offset: 2716
      Highwater::  0x01000a13  ext#: 1      blk#: 2      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 10
  mapblk  0x00000000  offset: 1
                  Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x01000a13  ext#: 1      blk#: 2      ext size: 8

--0x01000a13，也就是2579块，rollback，高水位点没有变化

在执行直接路径加载的过程中，高水位并没有真正提高，只有在事务提交后才会完成这个动作，在所有维护工作完成之后表才可以被访问

所以，在提交之前如果想查询这张表是不被允许的，同理可知对表的增删改以及merge操作也是不被允许的 

㈢ 直接路径加载与index

直接路径插入时，不产生表块的回滚信息，而是依赖高水位点实现回滚

但是，如果表有索引，将会产生索引的回滚信息，而且索引的块会被读进buffer cache

测试：

--为b表创建一个索引

hr@ORCL> create index idx_b on b (id);

Index created.

hr@ORCL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='IDX_B');

    FILE#    BLOCK#
---------- ----------
        4      2587  ←← 段头块
        4      2585  ←← L1块
        4      2588  ←← 第一个索引数据块
        4      2586  ←← L2块

--重启数据库，清空buffer cache

hr@ORCL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='IDX_B');

no rows selected

hr@ORCL> insert /*+ append */ into b select * from a;

4 rows created.

hr@ORCL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='IDX_B');

    FILE#    BLOCK#
---------- ----------
        4      2588
--直接路径插入时，索引块仍然会被调入buffer cache

hr@ORCL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn from v$transaction;

    XIDUSN    XIDSLOT    XIDSQN    UBAFIL    UBABLK    UBASQN
---------- ---------- ---------- ---------- ---------- ----------
        6        41        399          2      1456        261

--并且，对于索引块的修改，将会产生回滚信息，回滚信息保存在回滚块1456处
--因此，索引并不会"直接路径插入"
--因此，插入的索引数据，应该是在高水位点下：

hr@ORCL>  select header_file,header_block from dba_segments where segment_name='IDX_B';

HEADER_FILE HEADER_BLOCK
----------- ------------
          4        2587

hr@ORCL> alter system dump datafile 4 block 2587;

System altered.

--trc文件摘入如下：

  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8
                  last map  0x00000000  #maps: 0      offset: 2716
      Highwater::  0x01000a1d  ext#: 0      blk#: 4      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 1
  mapblk  0x00000000  offset: 0
                  Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x01000a1d  ext#: 0      blk#: 4      ext size: 8

--高水位点在2589块处，插入的索引数据在2588处，在高水位点下

Oracle官方文档建议，如果使用直接路径插入，向表中传送大量数据，可先将表上的索引删掉，插入结束后，再重新建立索引

 ㈣ 直接路径加载和一些限制

使用直接路径加载方法时需要注意的地方如下：

1）直接路径加载方法不是所有插入方式都支持的，最常见的带有value子句的insert语句就不支持

2）该技术最常用在insert into ... select ...结构的插入语句中

3）在使用直接路径加载技术插入数据直到事务提交，其他的增、删、改、查和merge操作是被禁止的

4）因为是直接路径加载，所以高水位以下的空闲数据库块将不被使用，可能会因此导致数据段无限扩张

5）当被操作的表上存在insert触发器、外键、IOT、表使用到了聚簇技术以及表中包含LOB字段时

直接路径加载技术是无效的，此时将会自动的转变为常规插入

㈤ 小结

使用直接路径加载技术之所以能提高性能是因为，该方法可以保证在加载数据的过程中最大限度的减少回滚数据的生成

expdp/impdp不支持直接路径，而sqlldr支持(由参数direct=true指定，并且同时指定parallel=true，速度将会更快)

如果我们在权衡利弊成本之后能最大化该方法，提高加载速度是必然的！