常规路径导出比直接路径导出快的问题（一）

前一阵在执行EXP导出的时候，发现居然EXP常规路径导出比EXP的直接路径导出速度还快。

当时导出情况大致如下：

$ exp data_demo/data_demo@zjtrade file=regular_data.dmp buffer=20480000

Export: Release 9.2.0.4.0 - Production on 星期一 6月 18 16:52:31 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 6月 18 17:04 regular_data.dmp

但是如果在此基础上增加一个DIRECT=Y的参数，采用直接路径导入的方式：

$ exp data_demo/data_demo@zjtrade file=direct_data.dmp buffer=20480000 direct=y

Export: Release 9.2.0.4.0 - Production on 星期一 6月 18 17:05:07 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 6月 18 18:26 direct_data.dmp

这个例子是在9204上执行的，客户端和服务器端版本都是9204。导出的时候如果采用常规路径，导出时间大约是12分钟。如果加上DIRECT=Y，那么导出时间居然增加到了1小时21分钟。

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

转载于:http://blog.itpub.net/4227/viewspace-69316/