小議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要差。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4227/viewspace-69148/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 小議IMP操作引數COMMIT=Y(三)MIT
- 小議IMP操作引數COMMIT=Y(二)MIT
- (轉載)小議IMP操作引數COMMIT=YMIT
- 理解exp, imp 使用direct=y 及imp commit=yMIT
- imp commit,buffer 引數MIT
- IMP中commit=y的用法~~~MIT
- imp commit引數的使用MIT
- importvg 一定要帶引數 -y vgname hdiksnameImport
- commit_write引數MIT
- imp的buffer和commit分析MIT
- 轉:oracle EXP /IMP引數詳解Oracle
- IMP EXP常用引數,實用例子
- 轉摘:ORACLE:IMP工具ignore=y的一個問題Oracle
- imp使用注意點(commit選項)MIT
- 小程式內引數和掃碼引數統一
- imp中的indexfile引數解決imp指定表空間問題Index
- 一個commit引發的思考MIT
- 【IMPDP】【IMP】SQL指令碼盡收眼底——SHOW引數與SQLFILE引數對比SQL指令碼
- ROUND(x,y)與TRUNC(x,y)函式中y引數值為省略、空值與null的區別函式Null
- mysql效能引數innodb_flush_log_at_trx_commitMySqlMIT
- EXP匯出引數compress=y(n)的區別
- commit_write,commit_logging,commit_wait引數和oracle redo行為MITAIOracle Redo
- 【IMP】使用IMP的SHOW引數輕鬆獲取EXP命令的DUMP檔案內容
- innodb_flush_log_at_trx_commit引數的直白理解MIT
- 關於對innodb_flush_log_at_trx_commit引數的一些理解MIT
- 常用操作 / 獲取引數
- [20171105]exp imp buffer引數解析.txt
- mysql的innodb_flush_log_at_trx_commit引數實驗MySqlMIT
- oracle 10g impdp與imp 引數選項不同地方Oracle 10g
- 常用URL引數操作方法
- oracle exp_imp小記Oracle
- pandas引數設定小技巧
- imp匯入時default 按table提交,使用commit按buffer提交MIT
- exp和imp的詳細操作
- [AlwaysOn] T-SQL語法:REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT引數SQLUIsynchronizedMIT
- MySQL:Innodb:innodb_flush_log_at_trx_commit引數影響的位置MySqlMIT
- exp匯出compress引數導致的imp時擴充套件太大套件
- exp/imp之@遠端匯出操作