晶晶实验二十二之 直接路径插入篇

晶晶小妹

一、直接路径插入与间接路径插入的不同
    这个问题相信很多人都已经知道了，为了方便初学者，我再来重审一遍。
create table 表1 as select 列1，列2，... select 表2
insert /*+append*/ into 表1 select 列1，列2，... select 表2
    如上形式的插入，都叫做直接路径插入。当然，在SQL*Loader中也有直接路径插入的形式。
    所谓直接路径插入，就是绕过Buffer cache，直接将数据插入进表所在数据文件中。
    假如有表AA，要将AA中的数据插入进表BB，在普通的间接插入下，先将AA的数据块传进Buffer cache，再将BB的块也传进Buffer cache，在Buffer cache中从AA的块中读出行，插入进BB的块中。BB的块就都变成了脏块，再等待DBWn把它们写进数据文件。因此，间接路径插入后，AA表的块和BB表的块都会在Buffer cache中出现。
    而直接路径插入下，将AA表的数据块传进Buffer cache中，读出行，直接写进BB表所在的数据文件。插入完毕后，除了表头块外，BB表的数据块并不会出现在Buffer cache中。
    下面来试验一下：
步1：准备试验用表：
SQL> create table aa(id number(4),name varchar2(5));
表已创建。
SQL> create table bb(id number(4),name varchar2(5));
表已创建。
SQL> insert into aa values(1,'aa');
已创建 1 行。
SQL> insert into aa values(2,'bb');
已创建 1 行。
SQL> insert into aa values(3,'cc');
已创建 1 行。
SQL> insert into aa values(4,'dd');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from aa;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                   4                                18493
                                   4                                18493
                                   4                                18493
                                   4                                18493
    现在AA表中有4行，占用块18493。BB表中没有数据。
步2：将buffer cache清空，我这里使用重启数据库的方法：
SQL> shutdown immediate
SQL> startup
步3：先用直接路径插入，从AA表向BB表插入数据：
SQL> insert /*+ append*/ into bb select * from aa;
已创建4行。
SQL> commit;
提交完成。
步4：使用V$bh查看Buffer cache中的块：
SQL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='AA');
     FILE#     BLOCK#
---------- ----------
         4      18491
         4      18491
         4      18494
         4      18492
         4      18495
         4      18493  <---- 当前包含数据的块
         4      18496
已选择7行。
由于对AA表进行了全表扫描，因此，AA表中高水点下的所有块都被读进了Buffer cache，这其中当然包括包含数据的块18493。
SQL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='BB');
     FILE#     BLOCK#
---------- ----------
         4      18499
         4      18499
         4      18497
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from bb;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                   4                                18500
                                   4                                18500
                                   4                                18500
                                   4                                18500
    上面两个查询可以看到，BB表中的数据占用第18500块，但是，直接路径插入后，18500块并没被调进Buffer cache。Buffer cache中只有18499和18497。 其中18499是段头块，而18497是L1块，直接路径插入后，要修改L1块中的数据块使用情况。
步5：再试一次间接路径插入：
SQL> insert into bb select * from aa;
已创建4行。
SQL> commit;
提交完成。
SQL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='BB');
     FILE#     BLOCK#
---------- ----------
         4      18504   <---- 本次间接路径插入的数据所在块
         4      18499
         4      18499
         4      18502
         4      18497
         4      18500
         4      18503
         4      18498
         4      18501
已选择9行。
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from bb;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                   4                                18500
                                   4                                18500
                                   4                                18500
                                   4                                18500
                                   4                                18504
                                   4                                18504
                                   4                                18504
                                   4                                18504
已选择8行。
    从上面的实验可以证明，间接路径插入，要先将数据块传进Buffer cache。这是Oracle通常修改数据的方式，不对数据文件直接进行修改，而是在内存中完成修改，再由日志提供保护。对于小量数据的修改，这种方法的性能还是很不错的。但是大量数据的修改，直接路径插入将可以提供更好的性能。
    直接路径插入除去少了将BB表的块传进Buffer cache这一步外，它还不产生回滚信息，下面来进一步的实验：
二、直接路径插入与回滚：
步1：再次向BB中直接路径插入：
SQL> insert /*+ append*/ into bb select id+4,name from aa;
步2：查看事务信息：
SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn from v$transaction;
    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN
---------- ---------- ---------- ---------- ---------- ----------
        11         23        854          0          0          0
    因为当前只有一个事务，因此选择 v$transaction 视图时没有加条件。从上面的显示结果可以看到，UBAFIL、UBABLK为0。也就是此事务并没有对应的回滚块，只在回滚段头的事务表中占用了一行而已。
    直接路径插入是如何提供回滚的呢？观察BB表高水点的变化，就可以解答这个问题：
步3： 查找BB表的高水点：
SQL> select header_file,header_block from dba_segments where segment_name='BB';
HEADER_FILE HEADER_BLOCK
----------- ------------
          4        18499
SQL> alter system dump datafile 4 block 18499;
系统已更改。
查找转储文件：
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 2      #blocks: 16   
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x01004849（高水点）  ext#: 0      blk#: 8      ext size: 8     
高水点是4号文件18505块。
步4：提交后查看直接路径插入到哪个块中：
SQL> commit;
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from bb where id>=5;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                   4                                18505
                                   4                                18505
                                   4                                18505
                                   4                                18505
本次插入的数据ID列值为5、6、7、8，通过上面的查询，本次直接路径插入，数据被存进18505号块。而提交前的高水点正是18505。
|--------------|--------|--------|----------
|数据块 ...... |18503|18504|18505
|--------------|--------|--------|----------
                                               ^
                                                |
                                                |
         此处是高水点，直接路径插入从此块开始分配空间
直接路径插入，是在高水点之上分配临时段，将数据插入时进此临时段中。在提交后将高水点提升至临时段之上。
现在已经提交，再查看高水点信息：
SQL> alter system dump datafile 4 block 18499;
系统已更改。
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 2      #blocks: 16   
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x0100484a（刚才是4849）  ext#: 1      blk#: 1      ext size: 8     
高水点升至18506块，如下图：
|--------------|--------|--------|--------|-----
|数据块 ...... |18503|18504|18505|18506
|--------------|--------|--------|--------|-----
                                                          ^
                                                           |
                                                           |
                                               高水点上升至此处
步5：再试一次直接路径插入回滚时的情况：
SQL> insert /*+ append*/ into bb select id+8,name from aa;
已创建4行。
猜想一下，此次插入应该插入进18506，如果提交的话，就提升高水点到18507，如果回滚的话，保持高水点不变。
查看高水点，当前仍是18506：
SQL> alter system dump datafile 4 block 18499;
系统已更改。
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 2      #blocks: 16   
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x0100484a  ext#: 1      blk#: 1      ext size: 8     
如果提交，肯定会变为18507，这个我们在步4已经被证明了，现在我们回滚：
SQL> rollback;
回退已完成。
现在已经回滚，查看高水点信息：
SQL> alter system dump datafile 4 block 18499;
系统已更改。
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 2      #blocks: 16   
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x0100484a  ext#: 1      blk#: 1      ext size: 8     
仍是18506。
也就是说：
|--------------|--------|--------|--------|--------|------
|数据块 ...... |18503|18504|18505|18506|18507
|--------------|--------|--------|--------|--------|-----
                                                        ^
                                                         |
                                                         |
                                                   高水点在此处
                                                 数据插入至此处
提交后，高水点升至18507，而如果回滚的话，高水点不变：
|--------------|--------|--------|--------|--------|------
|数据块 ...... |18503|18504|18505|18506|18507
|--------------|--------|--------|--------|--------|------
                                                          ^
                                                           |
                                                           |
                                            回滚后高水点仍在此处

三、直接路径插入与索引
直接路径插入时，不产生表块的回滚信息，依赖高水点实现回滚。但时，如果表有索引，将会产生索引的回滚信息，而且索引的块会被读时Buffer cache。也就是说，数据不能“直接插入”进索引。下面实验一下：
步1：为表BB创建一个索引：
SQL> create index bb_id on bb(id);
SQL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='BB_ID');
     FILE#     BLOCK#
---------- ----------
         4      18515  <--- 段头
         4      18513  <--- L1块
         4      18516  <--- 第一个索引数据块
         4      18514  <--- L2块
重启数据库，清空Buffer cache
步2：
SQL> insert /*+ append*/ into bb select * from aa;
已创建4行。
步3：
SQL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='BB_ID');
     FILE#     BLOCK#
---------- ----------
         4      18516
直接路径插入时，索引块将会被调入Buffer cache。
步4：
SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn from v$transaction;
    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN
---------- ---------- ---------- ---------- ---------- ----------
        12          9        548          5       2896        255
并且，对于索引块的修改，将会产生回滚信息，回滚信息保存在回滚块2896处。
因此，索引并不会“直接路径插入”，因此，插入的索引数据，应该是在高水点之下：
SQL> select header_file,header_block from dba_segments where segment_name='BB_ID';
HEADER_FILE HEADER_BLOCK
----------- ------------
          4        18515
SQL> alter system dump datafile 4 block 18515;
系统已更改。
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8     
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x01004855（高水点）  ext#: 0      blk#: 4      ext size: 8     
高水点在18517处。插入的索引数据在18515处，在高水点之下。
在文档中也曾建议，如果使用直接路径插入，向表中传送大量数据，可以先将表上的索引删掉，插入结束后，再重新建立索引。

[ 本帖最后由 晶晶小妹 于 2008-4-3 14:26 编辑 ]

netbanker

sofa

yuxuan

学习

tolywang

因此，索引并不会“直接路径插入”，因此，插入的索引数据，应该是在高水点之下：

........


高水点在18517处。插入的索引数据在18515处，在高水点之下。


-------------------



append 数据不需要经过buffer cache , , 直接在hwm以上insert 到数据文件 , 不产生undo信息 , 利用hwm回滚，回滚后hwm不回退

append 索引需要经过buffer cache, 但是是写入hwm之下的部分

这里    “ 索引并不会“直接路径插入  ”  ，而是通过buffer cache，能说明 索引数据(新插入的数据index)在高水点之下， 有些不明白， hwm之下有空间来插入这些新的大量的index数据 ，还是....... ？

feng_xin

louis_xu

顶

Yong Huang

Great posting! I'm learning from your posting.

Some minor comments. v$bh.objd is better matched against data_object_id. Some operations can change it to a value higher than object_id.

将buffer cache清空, why not alter system flush buffer cache (or ALTER SESSION SET EVENTS 'immediate trace name flush_cache' in 9i). I remember somebody posted a test that seems to show bouncing the database could actually pre-load some block buffers and manually flushing it removes this artifact. But I never chased down the root cause.

If you really want to avoid going through buffer cache even for the select part (which does a full table scan), you can alter session set "_serial_direct_read"=true before the insert select.

I've never tested but can you test setting _idl_conventional_index_maintenance to false and see if the index can also behave the same way as the table in direct path insert? (I don't have access to a database right now.)

Yong Huang

晶晶小妹

原帖由 Yong Huang 于 2008-4-3 12:13 发表
Great posting! I'm learning from your posting.

Some minor comments. v$bh.objd is better matched against data_object_id. Some operations can change it to a value higher than object_id.

将buffer cache清空, why not alter system flush buffer cache (or ALTER SESSION SET EVENTS 'immediate trace name flush_cache' in 9i). I remember somebody posted a test that seems to show bouncing the database could actually pre-load some block buffers and manually flushing it removes this artifact. But I never chased down the root cause.

If you really want to avoid going through buffer cache even for the select part (which does a full table scan), you can alter session set "_serial_direct_read"=true before the insert select.

I've never tested but can you test setting _idl_conventional_index_maintenance to false and see if the index can also behave the same way as the table in direct path insert? (I don't have access to a database right now.)

Yong Huang

关于 v$bh.objd ，你说的对，与data_object_id关联的确好一点。马上修改一下。我记得通常截断表后object_id会发生变化。
这两个隐藏参数的作用，_serial_direct_read、_idl_conventional_index_maintenance，我是第一次听说，晚上试验一上。谢谢你告诉我有这两个参数。

只所以没有使用 alter system flush buffer cache ， 我记得在以前的一次试验中，alter system flush buffer cache 只清除了部分块，而有许多块只是把LRU_FLAG状态（在X$BH中）设为4（在辅助LRU链中），TCH清零。
下面试验一下：
步1：全表扫描BB：
SQL> select * from bb;
        ID NAME
---------- -----
         1 aa
         2 bb
         3 cc
         4 dd
         1 aa
         2 bb
         3 cc
         4 dd
         1 aa
         2 bb
         3 cc
        ID NAME
---------- -----
         4 dd
         1 aa
         2 bb
         3 cc
         4 dd
         1 aa
         2 bb
         3 cc
         4 dd
已选择20行。

步2：查看Buffer cache中BB的块有哪些：
SQL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='BB');
     FILE#     BLOCK#
---------- ----------
         4      18504
         4      18499
         4      18507
         4      18502
         4      18497
         4      18505
         4      18500
         4      18503
         4      18498
         4      18506
         4      18501
已选择11行。

步3：刷新Buffer cache
SQL> alter system flush buffer_cache;
系统已更改。

步4：再次查看Buffer cache中BB的块有哪些
SQL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='BB');
     FILE#     BLOCK#
---------- ----------
         4      18504
         4      18499
         4      18507
         4      18502
         4      18497
         4      18505
         4      18500
         4      18503
         4      18498
         4      18506
         4      18501
已选择11行。

可以看到并没有少。不过现在Buffer cache中，所有的块的TCH都是0，LRU_FLAG状态都是4（说明块在辅助LRU链表），马上可以被覆盖。再次运行一遍步4的查询：

SQL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='BB');
     FILE#     BLOCK#
---------- ----------
         4      18504
         4      18502
         4      18505
         4      18500
         4      18503
         4      18498
         4      18506
         4      18501
已选择8行。

只剩下8个了，比刚才少三个。

显示X$BH可以看到：
SQL> select file#,dbablk,tch,lru_flag from x$bh where obj=(select data_object_id from dba_objects where object_name='BB' and owner='QSMED');
     FILE#     DBABLK        TCH   LRU_FLAG
---------- ---------- ---------- ----------
         4      18501          0          4
         4      18506          0          4
         4      18498          0          4
         4      18503          0          4
         4      18500          0          4
         4      18505          0          4
         4      18502          0          4
         4      18504          0          4
已选择8行。

每个块的TCH都变为了0，LRU_FLAG为4。

其实这时只需扫描一个大一点的表，这些LRU_FLAG为4的块就都会被清除或覆盖。不过重启动数据库命令简单些。

晶晶小妹

原帖由 tolywang 于 2008-4-3 10:39 发表
因此，索引并不会“直接路径插入”，因此，插入的索引数据，应该是在高水点之下：

........


高水点在18517处。插入的索引数据在18515处，在高水点之下。


-------------------



append 数据不需要经过buffer cache , , 直接在hwm以上insert 到数据文件 , 不产生undo信息 , 利用hwm回滚，回滚后hwm不回退

append 索引需要经过buffer cache, 但是是写入hwm之下的部分

这里    “ 索引并不会“直接路径插入  ”  ，而是通过buffer cache，能说明 索引数据(新插入的数据index)在高水点之下， 有些不明白， hwm之下有空间来插入这些新的大量的index数据 ，还是....... ？

从索引并不会直接路径插入，看不出来索引数据是在高水标记之下。这是个推论，所以需要用实验证明一下了。因为索引并没绕过Buffer cache，而且对索引的修改产生了回滚，因此猜想索引的数据并没有到高水点之上寻找空间，而是按照普通的插入，在高水点之下插入。实验也证明了此点。
设置_idl_conventional_index_maintenance 参数 为False后，索引应该也是“直接路径插入”了，这个参数我还没用过，在我的9i和10g中默认值都是True，我们可以试试。

[ 本帖最后由 晶晶小妹 于 2008-4-3 14:23 编辑 ]

晶晶小妹

YONG HUANG:我英文不好．．看的头晕．．
If you really want to avoid going through buffer cache even for the select part (which does a full table scan),

这句话中的ＥＶＥＮ，是不是只是做加强语气用的一个副词？可以翻译为甚至．或者不必翻译？