Speeding up datapump exports in 11.1.0.7

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 11.1.0.7  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 #           : 11.1.0.7.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 11.1.0.7 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 11.2.0.1 but the same test above on a couple of systems did not indicate any poor performance

About these ads