Oracle直接路徑載入–append的深度解析

        ㈠ 直接路徑載入和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        50514 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       2570hr@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                                 25768 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表的HWMhr@ORCL> select header_file,header_block from dba_segments where segment_name='B';HEADER_FILE HEADER_BLOCK----------- ------------          4         2571hr@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 cachehr@ORCL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='IDX_B');no rows selectedhr@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 cachehr@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         2587hr@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，速度將會更快)
          如果我們在權衡利弊成本之後能最大化該方法，提高載入速度是必然的！