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

清空資料，稍候繼續插入資料，來看看是否parallel被啟用了。

SQL> truncate table  big_table;

Table truncated.

Elapsed: 00:00:01.34

DML Parallelized                        0             1

DFO Trees                               0             1

Local Msgs Sent                         0         16191

Local Msgs Recv'd                       0         16191

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

Commit complete.

Elapsed: 00:00:01.92

DML Parallelized                        1             2

DFO Trees                               1             2

Server Threads                         16             0

Allocation Height                       8             0

Allocation Width                        1             0

Local Msgs Sent                    994995       1011186

Local Msgs Recv'd                  994995       1011186

在資料導入的過程中，啟用了相應的并行程序。

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資源充足的情況下，啟用并行也是一個不錯的選擇。