Oracle expdp匯出分割槽表資料慢的原因和解決方法

簡述：Oracle版本12.1.0.2在expdp時匯出分割槽表資料慢，遇到Bug 20236523 : DATAPUMP EXPORT SLOW USING CONTENT=METADATA_ONLY。

解決方法：第1種：升級至12.2.0.1；第2種：exp代替expdp。本文我們主要講第2種方法：exp代替expdp。

環境

os：redhat 7.3

db：12.1.0.2

1.原理通透

1.1 原理1

DATAPUMP EXPORT SLOW USING CONTENT=METADATA_ONLY慢的原因：

Oracle在有很多分割槽的物件中錯誤的使用了TABLE ACCESS BY INDEX ROWID

Rows (1st) Row Source Operation

---------- --------------------------------------------------- 1 FILT

1 TABLE ACCESS BY INDEX ROWID EXPDP_FCT_FR_FUND_ACTUAL_TXN (cr=4487

me=286592 us)

e=103328 us

)(object id 4703807)

The tkprof show that the new index is used, but that it is not selective

enough for objects with many partitions.

bde bug screening complete.

#Bug 20236523 - Datapump export is slow using CONTENT=metadata_only(Doc ID 20236523.8)

1.2 原理2

exp direct=y 最佳化原理：

使用直接路徑匯出，資料直接從磁碟讀取到匯出session的PGA中，從而跳過了SQL命令處理層

#Parameter DIRECT: Conventional Path Export Versus Direct Path Export (Doc ID 155477.1)

2.解決辦法兩種

2.1 升級版本

#效果

db：12.1.0.2 expdp 04:26:22

db：12.2.0.1 expdp 01:37:16

2.2 同版本，exp代替expdp

#效果

expdp 04:26:22

exp 00:12:00

實現方法

expdp 04:26:22

expdp \'sys/bhlbmc32 AS SYSDBA\' directory=dump dumpfile=expdp_bhl_schemas_${date}_%U.dmp schemas=BMCDB1,BMCDB2 METRICS=Y TRACE=480300 parallel=12 cluster=no logfile=expdp_bhl_schemas_${date}.log

exp 00:12:00

exp userid=\"sys/bhlbmc32@bmcCO1 as sysdba\" owner=BMCDB1,BMCDB2 direct=y recordlength=65535 buffer=1048576000 file=/oracle/app/oracle/dump/BMCDB1.20200411.dmp log=/oracle/app/oracle/dump/BMCDB1.20200411.log

##想明白為什麼，可以繼續讀

3.場景重新

3.1 原因分析：expdp為什麼那麼慢呢？

expdp \'sys/bhlbmc32 AS SYSDBA\' directory=dump dumpfile=expdp_bhl_schemas_${date}_%U.dmp schemas=BMCDB1,BMCDB2 METRICS=Y TRACE=480300 parallel=12 cluster=no logfile=expdp_bhl_schemas_${date}.log

#expdp使用METRICS=Y引數檢視每個object使用的時間，少於600秒也就是10分鐘的省略。大家可以看到涉及table一些統計是很慢的，這是一個值得研究的問題。

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Completed 365130 OBJECT_GRANT objects in 7705 seconds

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Completed 35606 COMMENT objects in 1098 seconds

Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT

Completed 25413 OBJECT_GRANT objects in 593 seconds

Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT

Completed 100435 OBJECT_GRANT objects in 2923 seconds

3.2 論據：官方MOS參考

透過mos查詢我們得知其中Bug 20236523 : DATAPUMP EXPORT SLOW USING CONTENT=METADATA_ONLY和我們的問題很相近

Bug 20236523 : DATAPUMP EXPORT SLOW USING CONTENT=METADATA_ONLY

MOS描述這個是一個錯誤的分割槽掃描引起的。

Rows (1st) Row Source Operation

---------- --------------------------------------------------- 1 FILT

1 TABLE ACCESS BY INDEX ROWID EXPDP_FCT_FR_FUND_ACTUAL_TXN (cr=4487

me=286592 us)

e=103328 us

)(object id 4703807)

The tkprof show that the new index is used, but that it is not selective

enough for objects with many partitions.

bde bug screening complete.

#可想而知一個大型分割槽表如果沒有選擇性，直接走TABLE ACCESS BY INDEX ROWID是很可怕的。分割槽本應該有選擇性的走分割槽，可現在確TABLE ACCESS BY INDEX ROWID（已索引單塊讀的方式，透過rowid去查詢所需的資料），當資料量越大效能消耗越大。

3.3 論點：exp代替expdp繞過這個BUG。我們知道了BUG的原理，生產業務不能立刻升級。我們只能用我們手中的工具來繞過這個BUG。

怎麼最佳化這個問題呢？expdp又不能hint執行計劃。其實全表掃描也是比TABLE ACCESS BY INDEX ROWID快的。進而想到了exp direct=y，這個不走SQL命令處理的方式。

#Direct path Export，直接匯出模式，資料直接從磁碟讀取到匯出session的PGA中，從而跳過了SQL命令處理層

3.4 論證：生產庫中確實有很多分割槽，符合MOS中BUG的描述。

反推如果資料庫中真的有很多分割槽，所以才導致BUG出現？經查生產庫中確實用了很多分割槽，所以導致Bug 20236523 : DATAPUMP EXPORT SLOW USING CONTENT=METADATA_ONLY的出現。

#統計分割槽表個數，這裡只統計了業務使用者

col OWNER for a40

select OWNER,count(*) from DBA_PART_TABLES group by OWNER;

OWNER COUNT(*)

---------------------------------------- ----------

BMCDB1 51

BMCDB3 64

#統計分割槽表分割槽個數，這裡只統計了業務使用者

col TABLE_OWNER for a40

select TABLE_OWNER,count(*) from DBA_TAB_PARTITIONS group by TABLE_OWNER;

TABLE_OWNER COUNT(*)

---------------------------------------- ----------

BMCDB1 881

BMCDB3 4048

3.5 論證：解決方案與效果

exp代替expdp，時間指標來看效能提升了22.5倍。也是當前各方人員都願意接受的方式。

expdp 04:26:22

exp 00:12:00

實現方法記錄

expdp 04:26:22

expdp \'sys/bhlbmc32 AS SYSDBA\' directory=dump dumpfile=expdp_bhl_schemas_${date}_%U.dmp schemas=BMCDB1,BMCDB2 METRICS=Y TRACE=480300 parallel=12 cluster=no logfile=expdp_bhl_schemas_${date}.log

exp 00:12:00

exp userid=\"sys/bhlbmc32@bmcCO1 as sysdba\" owner=BMCDB1,BMCDB2 direct=y recordlength=65535 buffer=1048576000 file=/oracle/app/oracle/dump/BMCDB1.20200411.dmp log=/oracle/app/oracle/dump/BMCDB1.20200411.log

總結：

遇到Oracle BUG 也不要害怕。明白BUG不能處理的原理或者邏輯。我們來用我所學的知識來繞過這BUG也是可以達到解決問題的效果的。

本文解決的思路：用exp代替expdp。把sql命令處理方式繞過。其實類似於用TABLE ACCESS FULL來代替TABLE ACCESS BY INDEX ROWID解決思路。

########################################################################################

QQ：14040928 E-mail：dbadoudou@163.com

本文連結： http://blog.itpub.net/26442936/viewspace-2685693/

#######################################################################################

Oracle 12.1.0.2 expdp匯出分割槽表資料遇到BUG慢的原因和解決方法

相關文章