发表于: 2007.06.19 18:05
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/296311
---------------------------------------------------------------
前一阵在执行EXP导出的时候，发现居然EXP常规路径导出比EXP的直接路径导出速度还快。
常规路径导出比直接路径导出快的问题（一）：http://yangtingkun.itpub.net/post/468/295932
这篇文章对比本地服务器二者执行时间。
上面的例子是通过客户端完成导出操作的，这里测试一下，在本地服务上进行导出时间的对比：
$ exp data_demo/data_demo file=regular_data.dmp buffer=20480000
Export: Release 9.2.0.4.0 - Production on Mon Jun 18 15:52:05 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit 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
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user DATA_DEMO
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user DATA_DEMO
About to export DATA_DEMO's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export DATA_DEMO's tables via Conventional Path ...
. . exporting table ACCESS_LOG 93888 rows exported
. . exporting table ASS_BBS_ARTICLE 0 rows exported
. . exporting table ASS_BBS_CATALOG 0 rows exported
. . exporting table ASS_BBS_USERINFO 0 rows exported
. . exporting table ASS_COMPLAIN_ORG_SCORE 0 rows exported
. . exporting table ASS_COMPLAIN_RECORD 0 rows exported
. . exporting table ASS_COMPLAIN_REPLY 0 rows exported
. . exporting table ASS_COMPLAIN_TYPE 0 rows exported
. . exporting table ASS_NEWS 2 rows exported
. . exporting table ASS_NEWS_TYPE 1 rows exported
. . exporting table A_CAT_BUYER 6283 rows exported
. . exporting table A_CAT_REGION_MERCHANDISE_BAK 12666 rows exported
.
.
.
. . exporting table Z_NEED_SEQS 55 rows exported
. . exporting table Z_REG_USER 0 rows exported
. . exporting table Z_USER_INFO 665 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
$ ls -l regular_data.dmp
-rw-r--r-- 1 oracle dba 2433254400 Jun 18 16:00 regular_data.dmp
从上面的结果可以看到，本地导出大概需要8分钟左右的时间，下面看看DIRECT=Y的直接路径方式：
$ exp data_demo/data_demo file=direct_data.dmp buffer=20480000 direct=y
Export: Release 9.2.0.4.0 - Production on Mon Jun 18 16:01:01 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit 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
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user DATA_DEMO
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user DATA_DEMO
About to export DATA_DEMO's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export DATA_DEMO's tables via Direct Path ...
. . exporting table ACCESS_LOG 93888 rows exported
. . exporting table ASS_BBS_ARTICLE 0 rows exported
. . exporting table ASS_BBS_CATALOG 0 rows exported
. . exporting table ASS_BBS_USERINFO 0 rows exported
. . exporting table ASS_COMPLAIN_ORG_SCORE 0 rows exported
. . exporting table ASS_COMPLAIN_RECORD 0 rows exported
. . exporting table ASS_COMPLAIN_REPLY 0 rows exported
. . exporting table ASS_COMPLAIN_TYPE 0 rows exported
. . exporting table ASS_NEWS 2 rows exported
. . exporting table ASS_NEWS_TYPE 1 rows exported
. . exporting table A_CAT_BUYER 6283 rows exported
. . exporting table A_CAT_REGION_MERCHANDISE_BAK 12666 rows exported
.
.
.
. . exporting table Z_NEED_SEQS 55 rows exported
. . exporting table Z_REG_USER 0 rows exported
. . exporting table Z_USER_INFO 665 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
$ ls -l direct_data.dmp
-rw-r--r-- 1 oracle dba 2411170816 Jun 18 16:07 direct_data.dmp
而这里行规路径导出方式没有让人失望，只用了6分钟左右就完成了导出操作。为什么本地导出和远端导出效率相差如此只大，其实原因在于直接路径导出的时候，没有设置正确的缓冲空间。
re: 常规路径导出比直接路径导出快的问题（二） 
