在載入大量資料的時候，可以考慮使用直接路徑載入技術來加速。不過該技術本身也存在一些弊端，在使用前需要權衡利弊。本文將透過實驗介紹該技術的一些點滴細節，供大家參考。

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 --

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

相關文章