Oracle DBA – A lifelong learning experience

Speeding up datapump exports in 11.1.0.7

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 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

4 Responses to “Speeding up datapump exports in 11.1.0.7”

  1. vkaminsky said

    Did you try playing with _SERIAL_DIRECT_READ and _SMALL_TABLE_THRESHOLD parameters? I believe changing them will enable direct reads to PGA as well as serialising the process for small tables – otherwise the process is forced to the parallel mode even if you specify the parallelism…

  2. Hi John
    Have you tried to debug the Data Pump session?

    http://kamranagayev.com/2013/03/11/debugging-data-pump-session/

    • John Hallas said

      Actually Kamran I did submit a trace file to Oracle from the datapump process when I raised an SR for this. I have just pulled some of the details out of the SR.

      I have traced the export worker and master process and the tkprof shows the wait is always on wait for unread message on broadcast channel
      Elapsed times include waiting on following events:

      Event waited on Times Max. Wait Total Waited
      ---------------------------------------- Waited ---------- ------------
      wait for unread message on broadcast channel
      1469 1.18 1435.04
      enq: TQ - DDL contention 1 1.15 1.15
      db file sequential read 27 0.04 0.42
      latch: row cache objects 2 0.00 0.00

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 185 other followers

%d bloggers like this: