Speeding up datapump exports in 220.127.116.11
Posted by John Hallas on March 19, 2013
I noticed that a small datapump export involving a few tables was taking more than 30 minutes. I tried enabling parallelism, which was a mistake as for small tables parallelism is not utilised even if the parameter is used in the parameter file – that is probably worth a blog in itself. I then assumed it was a disk performance issue until I tried it on other systems and realised it was quite a general thing.
I then came across bug 10153617 which is quite old (Sept 2010) but the 18.104.22.168 release is old now as well.
Interim Patch for Base Bug: 10153617
Date: Wed Sep 29 06:12:33 2010
Platform Patch for : Generic
Product Patched : ORACLE DATABASE
Product Version # : 22.214.171.124.0
RAC Rolling Installable : YES
Bugs Fixed by this patch:
6460304: EXPDP TAKES MORE TIME
7362589: GSIST12: EXPDP TABLE POOR PERFORMANCE OVERHEAD COMPARED TO ORIGINAL EXP
7710931: DATAPUMP EXPORT IS EXTREMELY SLOW WHEN EXTRACTING SCHEMA
7722575: DATAPUMP VIEW KU$_NTABLE_DATA_VIEW CAUSES POOR PLAN / SLOW EXPDP
I applied this as part of a general upgrade to PSU13 (it is not dependant upon any specific PSU) and the impact is very significant.
A simple test demonstrates the difference
create user test identified by xxxxxx default tablespace users; grant create session, create table to test; create table test.test as select * from sys.dba_objects exdp schemas=test directory=data_pump_dir content=ALL dumpfile=test.dmp logfile=test.log
The first datapump export took 20 minutes before I killed the job and the second finished in less than 2 minutes and I have seen this repeated on a number of systems. I applied this on top of PSU 13 with no problems and I would recommend reviewing any 126.96.36.199 databases where you might be exporting schemas or running a lot of datapump jobs. The test above or something similar shoudl provide an easy way of seeing if you are hitting the problem.
In the patch details above you will notice a bug 7710931 is also mentioned as being fixed by bug/patch 10153617. The detail of that suggests it is still valid in 188.8.131.52 but the same test above on a couple of systems did not indicate any poor performance