oracle 9i提供2种模式导出表数据性能比较

oracle 9i提供2种模式导出表数据，传统模式CONVENTIONAL PATH和直接模式DIRECT PATH，通过direct指定。

[@more@]

两者间差异

1. Conventional path Export.
传统导出模式使用SELECT语句抽取表数据,将数据从磁盘中读入到buffer cache缓冲区中,
并应用SQL表达式，将纪录返回给导出客户端，然后写到dump文件。

2. Direct path Export.
直接导出模式，数据直接从磁盘中读取到导出session的UGA中，
跳过了SQL命令处理层。避免了不必要的数据转换，然后将纪录返回给导出客户端，然后写到到处文件

性能比较:
1. Direct Export 比Conventional Export要快很多。在实际应用中，如果直接模式导出需要时间N，传统模式导出则需要2*N到3*N之间。
2. 当Direct Export 的时候设置大的RECORDLENGTH（length of IO record） 参数可以加快导出。最大64k，
3. Direct 模式导出并不影响导入数据的速度；导入数据与传统模式花费时间一样。
限制
1. 含有LOB对象的表不支持直接导出模式。
2. 直接导出不支持QUERY.
3. 直接导出模式使用RECORDLENGTH设置一次可以导出数据的量，传统模式使用buffer设置.
4. 低版本直接导出模式要求导出客户端和数据库字符集设置一致。

DIRECT PATH方式导出测试

机器配置:
普通PC 1U1G 普通硬盘.
数据库版本: Oracle9i Enterprise Edition Release 9.2.0.4

需求: 导出一张 11706993 记录的分区表 。

关于exp中buffer的设定.建议30--40%RAM。

导出后的dump文件为2.8G

1 指定buffer=400000000 direct=y . 历时5分1秒

[oracle@stardb1 oas]$ cat exp_XXXX.sh
export ORACLE_BASE=/oas
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=xxxx
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

#Set variable for this script.
logLocation='/oas/XXXX.log'

#Begin to dump.
echo $(date +"%y-%m-%d %H:%M:%S") >>$logLocation
echo "----------Begin to dump XXXX's data.-----------" >>$logLocation
exp sm23/sm23 tables=(XXXX) file=XXXX_with_direct.dmp buffer=400000000 direct=y statistics=none
echo "----------End to dump XXXX's data.-----------" >>$logLocation
echo $(date +"%y-%m-%d %H:%M:%S") >>$logLocation

[oracle@stardb1 oas]$ ./exp_XXXX.sh

Export: Release 9.2.0.4.0 - Production on Wed Aug 2 13:23:48 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16GBK character set and UTF8 NCHAR character set

About to export specified tables via Direct Path ...
. . exporting table XXXX
. . exporting partition XXXX200603 4062956 rows exported
. . exporting partition XXXX200604 4027679 rows exported
. . exporting partition XXXX200605
3894450 rows exported
. . exporting partition XXXX200606 3451088 rows exported
. . exporting partition XXXX200607
3409895 rows exported
. . exporting partition XXXX200608 165270 rows exported
. . exporting partition XXXX200609 0 rows exported
. . exporting partition XXXX200610 0 rows exported
Export terminated successfully without warnings.
[oracle@stardb1 oas]$ cat XXXX.log
06-08-02 13:23:48
----------Begin to dump XXXX's data.-----------
----------End to dump XXXX's data.-----------
06-08-02 13:28:49

2 指定 direct=y recordlength=65535 不指定buffer . 历时4分46秒

[oracle@stardb1 oas]$ cat exp_XXXX.sh
export ORACLE_BASE=/oas
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=xxxxx

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

#Set variable for this script.
logLocation='/oas/XXXX.log'

#Begin to dump.
echo $(date +"%y-%m-%d %H:%M:%S") >>$logLocation
echo "----------Begin to dump XXXX's data.-----------" >>$logLocation
exp sm23/sm23 tables=(XXXX) file=XXXX_with_direct.dmp recordlength=65535 direct=y statistics=none
echo "----------End to dump XXXX's data.-----------" >>$logLocation
echo $(date +"%y-%m-%d %H:%M:%S") >>$logLocation

[oracle@stardb1 oas]$ ./exp_XXXX.sh

Export: Release 9.2.0.4.0 - Production on Wed Aug 2 13:34:01 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16GBK character set and UTF8 NCHAR character set

About to export specified tables via Direct Path ...
. . exporting table XXXX
. . exporting partition XXXX200603
4062956 rows exported
. . exporting partition XXXX200604 4027679 rows exported
. . exporting partition XXXX200605
3894450 rows exported
. . exporting partition XXXX200606 3451088 rows exported
. . exporting partition XXXX200607
3409895 rows exported
. . exporting partition XXXX200608 166160 rows exported
. . exporting partition XXXX200609 0 rows exported
. . exporting partition XXXX200610 0 rows exported
Export terminated successfully without warnings.

06-08-02 13:34:01
----------Begin to dump XXXX's data.-----------
----------End to dump XXXX's data.-----------
06-08-02 13:38:55

结论:
对于导出操作,指定direct=y recordlength=65535效率最高.导出耗时依赖于机器配置.

通过设定buffer导入测试.

设定 buffer=400000000 实现数据导入 .历时9分43秒

1> 建立数据库schema

SQL> create user test identified by test default tablespace BUSINESS;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

2> 导入脚本

[oracle@stardb1 oas]$ cat imp_test.sh
export ORACLE_BASE=/oas
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=xxxxx

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

#Set variable for this script.
logLocation='/oas/xxxxx.log'

#Begin to dump.
echo $(date +"%y-%m-%d %H:%M:%S") >>$logLocation
echo "----------Begin to dump xxxxs data.-----------" >>$logLocation
imp test/test tables=(xxxx) file=xxxx_with_direct.dmp buffer=400000000 indexes=y
echo "----------End to dump xxxx data.-----------" >>$logLocation
echo $(date +"%y-%m-%d %H:%M:%S") >>$logLocation

06-08-02 14:30:38
----------Begin to imp xxxx's data.-----------
----------End to imp xxxx's data.-----------
06-08-02 14:40:21

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

转载于:http://blog.itpub.net/76065/viewspace-852681/