expdp performance

[Roshan]

Oracle database 12c
OS: AIX

Hello Erman,

kindly advise what could be the reason for schema level backup to be slower than full backup(full=y)? Schema level backup takes around 1 day while full backup takes around 5 hours.

I would like to know why schema backup is taking longer (a day) to complete while a full backup (full=y) takes only 5 hours to complete?

schema backup script:

expdp system/******** schemas=AUTHENTICATION directory=REFRESH dumpfile=dtpdbprod_${DATE}_AUTHENTICATION.dmp logfile=dtpdbprod_${DATE}_AUTHENTICATION.elog
expdp system/******** schemas=BB_REPORTING directory=REFRESH dumpfile=dtpdbprod_${DATE}_BB_REPORTING.dmp logfile=dtpdbprod_${DATE}_BB_REPORTING.elog
expdp system/******** schemas=CATALYST directory=REFRESH dumpfile=dtpdbprod_${DATE}_CATALYST.dmp logfile=dtpdbprod_${DATE}_CATALYST.elog
expdp system/******** schemas=CIBLENDINGCAMUNDAPROD directory=REFRESH dumpfile=dtpdbprod_${DATE}_CIBLENDINGCAMUNDAPROD.dmp logfile=dtpdbprod_${DATE}_CIBLENDINGCAMUNDAPROD.elog
expdp system/******** schemas=CIBLENDINGPROD directory=REFRESH dumpfile=dtpdbprod_${DATE}_CIBLENDINGPROD.dmp logfile=dtpdbprod_${DATE}_CIBLENDINGPROD.elog
expdp system/******** schemas=CIBLEND_DWH directory=REFRESH dumpfile=dtpdbprod_${DATE}_CIBLEND_DWH.dmp logfile=dtpdbprod_${DATE}_CIBLEND_DWH.elog
expdp system/******** schemas=CIBPAYMENT directory=REFRESH dumpfile=dtpdbprod_${DATE}_CIBPAYMENT.dmp logfile=dtpdbprod_${DATE}_CIBPAYMENT.elog
expdp system/******** schemas=DASHBOARD directory=REFRESH dumpfile=dtpdbprod_${DATE}_DASHBOARD.dmp logfile=dtpdbprod_${DATE}_DASHBOARD.elog
expdp system/******** schemas=ESIGPROD directory=REFRESH dumpfile=dtpdbprod_${DATE}_ESIGPROD.dmp logfile=dtpdbprod_${DATE}_ESIGPROD.elog
expdp system/******** schemas=FILES directory=REFRESH dumpfile=dtpdbprod_${DATE}_FILES.dmp logfile=dtpdbprod_${DATE}_FILES.elog
expdp system/******** schemas=FILESERVERPROD directory=REFRESH dumpfile=dtpdbprod_${DATE}_FILESERVERPROD.dmp logfile=dtpdbprod_${DATE}_FILESERVERPROD.elog
expdp system/******** schemas=FLOWS_FILES directory=REFRESH dumpfile=dtpdbprod_${DATE}_FLOWS_FILES.dmp logfile=dtpdbprod_${DATE}_FLOWS_FILES.elog
expdp system/******** schemas=HOMELOANS directory=REFRESH dumpfile=dtpdbprod_${DATE}_HOMELOANS.dmp logfile=dtpdbprod_${DATE}_HOMELOANS.elog
expdp system/******** schemas=INSURANCE directory=REFRESH dumpfile=dtpdbprod_${DATE}_INSURANCE.dmp logfile=dtpdbprod_${DATE}_INSURANCE.elog
expdp system/******** schemas=MDDATA directory=REFRESH dumpfile=dtpdbprod_${DATE}_MDDATA.dmp logfile=dtpdbprod_${DATE}_MDDATA.elog
expdp system/******** schemas=MORATORIUM directory=REFRESH dumpfile=dtpdbprod_${DATE}_MORATORIUM.dmp logfile=dtpdbprod_${DATE}_MORATORIUM.elog
expdp system/******** schemas=PCI_READ directory=REFRESH dumpfile=dtpdbprod_${DATE}_PCI_READ.dmp logfile=dtpdbprod_${DATE}_PCI_READ.elog
expdp system/******** schemas=PERFSTAT directory=REFRESH dumpfile=dtpdbprod_${DATE}_PERFSTAT.dmp logfile=dtpdbprod_${DATE}_PERFSTAT.elog
expdp system/******** schemas=REMOTEKYC directory=REFRESH dumpfile=dtpdbprod_${DATE}_REMOTEKYC.dmp logfile=dtpdbprod_${DATE}_REMOTEKYC.elog
expdp system/******** schemas=SMEMARKETPLACEPROD directory=REFRESH dumpfile=dtpdbprod_${DATE}_SMEMARKETPLACEPROD.dmp logfile=dtpdbprod_${DATE}_SMEMARKETPLACEPROD.elog
expdp system/******** schemas=SMEONBOARDING directory=REFRESH dumpfile=dtpdbprod_${DATE}_SMEONBOARDING.dmp logfile=dtpdbprod_${DATE}_SMEONBOARDING.elog
expdp system/******** schemas=USR_SME_READ directory=REFRESH dumpfile=dtpdbprod_${DATE}_USR_SME_READ.dmp logfile=dtpdbprod_${DATE}_USR_SME_READ.elog
expdp system/******** schemas=SMELENDING directory=REFRESH dumpfile=dtpdbprod_${DATE}_SMELENDING.dmp logfile=dtpdbprod_${DATE}_SMELENDING.elog
expdp system/******** schemas=mcib directory=REFRESH dumpfile=dtpdbprod_${DATE}_mcib.dmp logfile=dtpdbprod_${DATE}_mcib.elog
expdp system/******** schemas=ESIGPROD directory=REFRESH dumpfile=dtpdbprod_${DATE}_ESIGPROD.dmp logfile=dtpdbprod_${DATE}_ESIGPROD.elog

tar cvpf - dtpdbprod_${DATE}_AUTHENTICATION* |compress > dtpdbprod_${DATE}_AUTHENTICATION.tar.Z
tar cvpf - dtpdbprod_${DATE}_BB_REPORTING* |compress > dtpdbprod_${DATE}_BB_REPORTING.tar.Z
tar cvpf - dtpdbprod_${DATE}_CATALYST* |compress > dtpdbprod_${DATE}_CATALYST.tar.Z
tar cvpf - dtpdbprod_${DATE}_CIBLENDINGCAMUNDAPROD* |compress > dtpdbprod_${DATE}_CIBLENDINGCAMUNDAPROD.tar.Z
tar cvpf - dtpdbprod_${DATE}_CIBLENDINGPROD* |compress > dtpdbprod_${DATE}_CIBLENDINGPROD.tar.Z
tar cvpf - dtpdbprod_${DATE}_CIBLEND_DWH* |compress > dtpdbprod_${DATE}_CIBLEND_DWH.tar.Z
tar cvpf - dtpdbprod_${DATE}_CIBPAYMENT* |compress > dtpdbprod_${DATE}_CIBPAYMENT.tar.Z
tar cvpf - dtpdbprod_${DATE}_DASHBOARD* |compress > dtpdbprod_${DATE}_DASHBOARD.tar.Z
tar cvpf - dtpdbprod_${DATE}_ESIGPROD* |compress > dtpdbprod_${DATE}_ESIGPROD.tar.Z
tar cvpf - dtpdbprod_${DATE}_FILES.* |compress > dtpdbprod_${DATE}_FILES.tar.Z
tar cvpf - dtpdbprod_${DATE}_FILESERVERPROD* |compress > dtpdbprod_${DATE}_FILESERVERPROD.tar.Z
tar cvpf - dtpdbprod_${DATE}_FLOWS_FILES* |compress > dtpdbprod_${DATE}_FLOWS_FILES.tar.Z
tar cvpf - dtpdbprod_${DATE}_HOMELOANS* |compress > dtpdbprod_${DATE}_HOMELOANS.tar.Z
tar cvpf - dtpdbprod_${DATE}_INSURANCE* |compress > dtpdbprod_${DATE}_INSURANCE.tar.Z
tar cvpf - dtpdbprod_${DATE}_MDDATA* |compress > dtpdbprod_${DATE}_MDDATA.tar.Z
tar cvpf - dtpdbprod_${DATE}_MORATORIUM* |compress > dtpdbprod_${DATE}_MORATORIUM.tar.Z
tar cvpf - dtpdbprod_${DATE}_PCI_READ* |compress > dtpdbprod_${DATE}_PCI_READ.tar.Z
tar cvpf - dtpdbprod_${DATE}_PERFSTAT* |compress > dtpdbprod_${DATE}_PERFSTAT.tar.Z
tar cvpf - dtpdbprod_${DATE}_REMOTEKYC* |compress > dtpdbprod_${DATE}_REMOTEKYC.tar.Z
tar cvpf - dtpdbprod_${DATE}_SMEMARKETPLACEPROD* |compress > dtpdbprod_${DATE}_SMEMARKETPLACEPROD.tar.Z
tar cvpf - dtpdbprod_${DATE}_SMEONBOARDING* |compress > dtpdbprod_${DATE}_SMEONBOARDING.tar.Z
tar cvpf - dtpdbprod_${DATE}_USR_SME_READ* |compress > dtpdbprod_${DATE}_USR_SME_READ.tar.Z
tar cvpf - dtpdbprod_${DATE}_SMELENDING* |compress > dtpdbprod_${DATE}_SMELENDING.tar.Z
tar cvpf - dtpdbprod_${DATE}_ZABBIX* |compress > dtpdbprod_${DATE}_ZABBIX.tar.Z
tar cvpf - dtpdbprod_${DATE}_SPOTUSER* |compress > dtpdbprod_${DATE}_SPOTUSER.tar.Z
tar cvpf - dtpdbprod_${DATE}_mcib* |compress > dtpdbprod_${DATE}_mcib.tar.Z
tar cvpf - dtpdbprod_${DATE}_ESIGPROD* |compress > dtpdbprod_${DATE}_ESIGPROD.tar.Z
Kindly advise how to improve performance?

Regards,

Roshan

[Roshan]

Assume par file:

userid=system/****
dumpfile=DTP_%U.dmp
directory=DP
full=y
logfile=exp_DTP.elog
parallel=4

[ErmanArslansOracleBlog]

It may be caused by repeated queries, for checking objects to export, for calculating things like Estimated Times and etc..

The thing that makes me think that way is the faster full export.. This means when you do things in one go, it completes faster.. So, probably there is something other than export itself.. its queries, its checks and etc..

Still, you have a workaround.. Full export is not a bad thing ( and you got lots of schema to export) :)

If I were you, I would monitor database sessions of those export processes + I would check the following MOS notes ->

Checklist For Slow Performance Of DataPump Export (expdp) And Import (impdp) (Doc ID 453895.1)
DataPump Export (EXPDP) Performance Known Problems (Doc ID 1941760.1)

[ErmanArslansOracleBlog]

+And, there are tar (Tape Archive) commands at the end of the script, they may be taking time also.. Ensure what is taking time and then concentrate on the diagnosis.

[Roshan]

Thanks a lot