ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 小议IMP操作参数COMMIT=Y(一)

小议IMP操作参数COMMIT=Y(一)

原创 Linux操作系统 作者:yangtingkun 时间:2007-01-24 00:00:00 0 删除 编辑

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


个人认为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要差。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-69148/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10487296