oracle commit=y,小议IMP操作参数COMMIT=Y（二）

前一段时间在论坛上看到imp的COMMIT参数问题，于是打算简单总结一下。

这篇文章进一步讨论COMMIT=Y对性能的影响。

上一篇文章讨论了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列似乎采用的就是数组插入的方式，怀疑文档这里有误。