小議IMP操作引數COMMIT=Y（一）

前一段時間在論壇上看到imp的COMMIT引數問題，於是打算簡單總結一下。

個人認為COMMIT=Y唯一的優點在於可以避免大資料量匯入的時候，對回滾段造成的壓力，除此之外再無優點可言。這篇文章主要討論COMMIT=Y的缺點。

設定了COMMIT=Y首先會遇到的問題是效率問題：

在Oracle中頻繁的COMMIT資料不但不會帶來效能的提升，反而會降低COMMIT的效能。下面看一個PL/SQL的例子：

SQL> CREATE TABLE T (ID NUMBER);

表已建立。

SQL> SET TIMING ON
SQL> BEGIN
2 FOR I IN 1..1000000 LOOP
3 INSERT INTO T VALUES (I);
4 END LOOP;
5 COMMIT;
6 END;
7 /

PL/SQL 過程已成功完成。

已用時間: 00: 01: 25.03
SQL> BEGIN
2 FOR I IN 1..1000000 LOOP
3 INSERT INTO T VALUES (I);
4 COMMIT;
5 END LOOP;
6 END;
7 /

PL/SQL 過程已成功完成。

已用時間: 00: 02: 48.10

透過上面的例子可以明顯的看出頻繁的COMMIT對效能的影響。這種影響也會體現在IMP當中。

SQL> set time on
19:35:26 SQL> host exp test/test file=test.dmp tables=t

Export: Release 9.2.0.4.0 - Production on 星期三 1月 24 19:35:33 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 2000000 rows exported
Export terminated successfully without warnings.

19:35:35 SQL> drop table t;

Table dropped.

19:35:40 SQL> host imp test/test file=test.dmp tables=t

Import: Release 9.2.0.4.0 - Production on 星期三 1月 24 19:35:42 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" 2000000 rows imported
Import terminated successfully without warnings.

19:35:46 SQL> drop table t;

Table dropped.

19:35:50 SQL> host imp test/test file=test.dmp tables=t commit=y

Import: Release 9.2.0.4.0 - Production on 星期三 1月 24 19:35:54 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" 2000000 rows imported
Import terminated successfully without warnings.

19:35:59 SQL>

透過IMP匯入時，如果設定了COMMIT=Y，效能明顯會比不設定COMMIT=Y要差。