巧用parallel極速提升資料載入速度

並行在平時工作中可能不是很注意，因為有時候即使設定了parallel 相關的hint，感覺效能也好不到哪去。這是我以前的感覺。
今天透過一個案例來分享一下透過parallel來使資料載入的速度達到極速提升。
現有一個很讓人頭疼的表，裡面還有clob欄位，透過exp/imp來匯出匯入資料，匯出慢還可以接受，匯入的速度大概在一秒鐘1000條的速度，對於千萬，上億的資料來說，簡直就是噩夢。對於資料泵，也測試了各種可能的改進方法。但是效果都不讓人滿意，首先就是對於undo的消耗極大，還有impdp中parallel選項因為clob無法啟用。在測試環境中反覆測試，時間大概保持在2個小時的樣子（資料量是5千萬),而且還得不斷的去檢視undo的使用率，有一次測試中還報了undo空間不足的錯誤，整個資料匯入得重頭再來，而且還使得高水位線受到影響。
個人反覆的嘗試，最後使用外部表來進行資料的分批匯入，這樣能夠降低undo使用率，對於進度也比較好把握，比如對於大表big_table，我生成了20個外部表，把big_table裡的資料分攤到了20個外部表中，這樣每個外部表做完insert之後，馬上commit，可以減少undo使用競爭。測試環境中測試，時間在40~60分鐘左右，剛開始的時候速度很快，一分鐘將近160萬的資料載入速度，但是到後面速度就開始逐漸降下來了。最後150萬的資料基本在5分鐘左右。
一方面是外部原因，另一方面和資料庫內部的機理也有關聯，有些塊不會很快的釋放。

使用外部表Insert的方式效能要好一些，但是得改進一些地方，尤其是對於大表來說，parallel比想象中的效果要好很多，

首先來看一下一般的資料插入速度。速度在40秒左右。為了保證測試的可評估性，我每次都會換一個資料量基本一致的外部表來插入資料。
SQL>  insert into big_table select *from big_table_ext_33;
820374 rows created.

Elapsed: 00:00:40.80
SQL> commit;

嘗試使用append方式插入資料，表big_table已經設定為nologging模式，有4個local partitioned 的index,都是logging模式。
速度一下子提升了不少達到了16秒。
SQL> insert /*+append*/ into big_table select *from big_table_ext_30;
960461 rows created.

Elapsed: 00:00:16.11

然後繼續換一個表，使用parallel hint來插入資料。但是時間好像沒有任何提升。
SQL> insert /*+append parallel(big_table 8) */ into big_table select *from big_table_ext_2;
988140 rows created.

Elapsed: 00:00:16.14
這個時候可以使用v$pq_sesstat來檢視使用parallel被啟用了。
SQL> select * from v$pq_sesstat;

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    0             0
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               0             0
Server Threads                          0             0
Allocation Height                       0             0
Allocation Width                        0             0
Local Msgs Sent                         0             0
Distr Msgs Sent                         0             0
Local Msgs Recv'd                       0             0
Distr Msgs Recv'd                       0             0
11 rows selected.
可以看到parallel的hint被oracle給忽略了。parallel dml的優先順序是session >hint> object
所以繼續設定session級的hint，啟用parallel，這個時候如果想保證啟用paralell可以使用force選項。
SQL> alter session force parallel dml parallel 8;
Session altered.
Elapsed: 00:00:00.00

再次插入資料，時間一下子降低到了6秒鐘。
SQL> insert /*+append parallel(big_table 8) */ into big_table select *from big_table_ext_31;
930198 rows created.

Elapsed: 00:00:06.49
SQL> commit;

清空資料，稍候繼續插入資料，來看看是否parallel被啟用了。
SQL> truncate table  big_table;
Table truncated.

Elapsed: 00:00:01.34
SQL> select * from v$pq_sesstat;

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    0             0
DML Parallelized                        0             1
DDL Parallelized                        0             0
DFO Trees                               0             1
Server Threads                          0             0
Allocation Height                       0             0
Allocation Width                        0             0
Local Msgs Sent                         0         16191
Distr Msgs Sent                         0             0
Local Msgs Recv'd                       0         16191
Distr Msgs Recv'd                       0             0

11 rows selected.

將近100萬的資料在6秒鐘匯入了，如果是5000萬的資料大概需要6分鐘左右的時間，來簡單驗證一下
做一個大的資料插入。大概用了7分鐘的時間，速度還是不錯的。
SQL> insert /*+append parallel(big_table 8) */ into big_table select *from big_table_ext;
58303757 rows created.
Elapsed: 00:07:26.48
SQL> commit;
Commit complete.
Elapsed: 00:00:01.92

SQL> select * from v$pq_sesstat;
STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    0             0
DML Parallelized                        1             2
DDL Parallelized                        0             0
DFO Trees                               1             2
Server Threads                         16             0
Allocation Height                       8             0
Allocation Width                        1             0
Local Msgs Sent                    994995       1011186
Distr Msgs Sent                         0             0
Local Msgs Recv'd                  994995       1011186
Distr Msgs Recv'd                       0             0

11 rows selected.

在資料匯入的過程中，啟用了相應的並行程式。
26388 testdbn  15   0 11.5g 1.4g 1.1g S 38.3  0.4   2:41.80 ora_p012_TESTDB                                                                               
26394 testdbn  15   0 11.5g 1.4g 1.1g S 38.3  0.4   2:40.87 ora_p015_TESTDB                                                                               
26380 testdbn  15   0 11.5g 1.4g 1.2g S 37.6  0.4   3:50.61 ora_p008_TESTDB                                                                               
26390 testdbn  15   0 11.5g 1.4g 1.1g S 37.6  0.4   2:44.64 ora_p013_TESTDB                                                                               
26392 testdbn  15   0 11.5g 1.4g 1.1g S 37.3  0.4   2:43.63 ora_p014_TESTDB                                                                               
26382 testdbn  15   0 11.5g 1.4g 1.1g S 37.0  0.4   2:43.43 ora_p009_TESTDB                                                                               
 7080 testdbn  16   0 11.3g  68m  28m S  9.7  0.0   2:38.05 ora_arc2_TESTDB                                                                               
 4101 testdbn  15   0 11.2g 6.1g 6.1g S  6.1  1.7  10:54.46 ora_dbw1_TESTDB                                                                               
 4105 testdbn  15   0 11.2g 6.1g 6.1g S  6.1  1.7  11:27.73 ora_dbw3_TESTDB                                                                               
 4099 testdbn  15   0 11.2g 6.1g 6.1g R  5.8  1.7  11:49.40 ora_dbw0_TESTDB                                                                               
 4103 testdbn  15   0 11.2g 6.1g 6.1g S  4.8  1.7  10:47.55 ora_dbw2_TESTDB

所以在cpu資源充足的情況下，啟用並行也是一個不錯的選擇。