(轉載)小議IMP操作引數COMMIT=Y

 

前一段時間在論壇上看到imp的COMMIT引數問題，於是打算簡單總結一下。
這篇文章進一步討論COMMIT=Y對效能的影響。
小議IMP操作引數COMMIT=Y（一）：http://yangtingkun.itpub.net/post/468/254854

上一篇文章討論了COMMIT=Y對效能的影響。有人也許會說，COMMIT=Y不會針對每條記錄去COMMIT的，而是針對BUFFER中容納的所有資料。可以透過加大BUFFER的方式來避免COMMIT=Y帶來的效能影響。
這個觀點有一定的道理，這也說明了前一篇文章例子中，為什麼COMMIT對IMP效率的影響沒有對PL/SQL例子的影響那麼明顯。
但是這個觀點有一個前提。那就是表中不能包含LONG、LOB、REF、ROWID和UROWID列。如果包含了這些列，那麼陣列繫結插入將失效，每條記錄的插入之後都會執行COMMIT操作。
所以，設定了COMMIT=Y後，包含這些列的表的匯入效能會嚴重下降。
SQL> CREATE TABLE T (ID NUMBER, COMMENTS CLOB);
表已建立。
SQL> BEGIN
 2 FOR I IN 1..100000 LOOP
 3 INSERT INTO T VALUES (I, TO_CHAR(I));
4 END LOOP;
 5 COMMIT;
6 END;
 7 /
PL/SQL 過程已成功完成。
SQL> host exp test/test file=test.dmp tables=t
Export: Release 9.2.0.4.0 - Production on 星期四 1月 25 20:32:25 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 – Production 
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T 100000 rows exported
Export terminated successfully without warnings.
SQL> set time on
20:32:47 SQL> drop table t;
Table dropped.
20:32:51 SQL> host imp test/test file=test.dmp tables=t buffer=20480000
Import: Release 9.2.0.4.0 - Production on 星期四 1月 25 20:32:53 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. . importing table "T" 100000 rows imported
Import terminated successfully without warnings.
20:33:34 SQL> drop table t;
Table dropped.
20:33:40 SQL> host imp test/test file=test.dmp tables=t buffer=20480000 commit=y
Import: Release 9.2.0.4.0 - Production on 星期四 1月 25 20:33:41 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. . importing table "T" 100000 rows imported
Import terminated successfully without warnings.
20:35:34 SQL>
不使用COMMIT=Y需要43秒，而採用了COMMIT=Y則需要54秒。因此，對於包含這些列的表來說，COMMIT=Y的影響更大一些。
最後簡單說一句，Oracle的文件中除了上面的這些列，還提到了DATE列，但是根據我的測試發現，DATE列似乎採用的就是陣列插入的方式，懷疑文件這裡有誤。