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