oracle 9i导出用户表,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