【INSERT】逐行提交、批量提交及極限提速方法

賀子_DBA時代發表於2014-08-07
在Oracle資料庫中，不是提交越頻繁越好。恰恰相反，批量提交可以得到更好的效能。這篇文章給大家簡單展示一下在Oracle資料庫中逐行提交於批量提交兩者之間的效能差別。最後再給出一種可以極大改變效能的方法。

1.建立表t_ref，並初始化880992條資料，用於後面的實驗。
sec@ora10g> create table t_ref as select * from all_objects;

sec@ora10g> insert into t_ref select * from t;

220248 rows created.

sec@ora10g> /
sec@ora10g> /
sec@ora10g> /
sec@ora10g> /
sec@ora10g> /

440496 rows created.

2.編寫一個簡單的PL/SQL塊來模擬逐行提交的情況，注意觀察執行時間。
我們的目標是將t_ref表中的資料全部插入到t中。
sec@ora10g> set timing on

sec@ora10g> DECLARE
  2  BEGIN
  3    FOR cur IN (SELECT * FROM t_ref) LOOP
  4      INSERT INTO t VALUES cur;
  5      COMMIT;
  6    END LOOP;
  7  END;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:03:12.77

逐行提交的情況下，一共用時3分12秒。

3.再來模擬批量提交的情況。
sec@ora10g> truncate table t;

Table truncated.

sec@ora10g> DECLARE
  2    v_count NUMBER;
  3  BEGIN
  4    FOR cur IN (SELECT * FROM t_ref) LOOP
  5      INSERT INTO t VALUES cur;
  6      v_count := v_count + 1;
  7      IF v_count >= 100 THEN
  8        COMMIT;
  9      END IF;
 10    END LOOP;
 11    COMMIT;
 12  END;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:27.69

此時共用時1分27秒，大約是逐行提交方法一半的時間。由此可見，對於Oracle應該儘量以批量提交的方式來完成工作。

4.最後我們使用Oracle更高階的方法完成上面的任務，體驗一下極限速度。
sec@ora10g> DECLARE
  2    CURSOR cur IS
  3      SELECT * FROM t_ref;
  4    TYPE rec IS TABLE OF t_ref%ROWTYPE;
  5    recs rec;
  6  BEGIN
  7    OPEN cur;
  8    WHILE (TRUE) LOOP
  9      FETCH cur BULK COLLECT
 10        INTO recs LIMIT 100;
 11      FORALL i IN 1 .. recs.COUNT
 12        INSERT INTO t VALUES recs (i);
 13      COMMIT;
 14      EXIT WHEN cur%NOTFOUND;
 15    END LOOP;
 16    CLOSE cur;
 17  END;
 18  /

Elapsed: 00:00:09.75

此時我們僅僅使用了不到10秒的時間就完成了曾經需要幾分鐘才能完成的任務。

5.小結
在Oracle資料庫中，頻繁的COMMIT會引起大量Redo Log的物理I/O，會極大的限制資料庫的效能。因此，為提高資料庫效能，儘可能的批量提交。
Oracle有很多優秀的方法值得嘗試。

Good luck.

-- The End --

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29654823/viewspace-1247523/，如需轉載，請註明出處，否則將追究法律責任。

相關文章