DataPump Performance Slow On Multithread CPU

(Doc ID 1385135.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.5 and later
Oracle Solaris on SPARC (64-bit)
***Checked for relevance on 19-Jun-2013***

Symptoms

When running a DataPump export job with only one worker (DW, for example, when metadata is extracted), the performance is slow on multithread CPU.

Hardware configuration: Sun SPARC Systems Sun-Fire-T200 CPU

DataPump export log:

;;;
Export: Release 11.2.0.3.0 - Production on Wed Nov 23 10:50:36 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_04":  system/******** directory=DATAPUMP dumpfile=test.dmp logfile=test.log schemas=TEST parallel=32 cluster=n METRICS=Y CONTENT=METADATA_ONLY
Processing object type SCHEMA_EXPORT/USER
     Completed 1 USER objects in 3 seconds
.
.
.
Processing object type SCHEMA_EXPORT/JOB
     Completed 5 JOB objects in 17 seconds
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_04 is:
  /test.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_04" successfully completed at 13:05:30


This shows that the DataPump export of metadata took 2 hours and 15 minutes.

The AWR report shows high CPU (only DataPump runs in the database):

Top 5 Timed Foreground Events
Event                    Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU                              48                   90.99
db file sequential read  1,268       5            4      10.24   User I/O
db file scattered read     182       1            4       1.47   User I/O


Run the following query to identify database worker (DW):

connect / as sysdba

SELECT pid, spid, program
FROM   v$process
WHERE  program LIKE '%DW%';


The output is similar to:

PID SPID PROGRAM
--- ---- ------------------------------------
 26 3571 oracle@celcsol1.us.oracle.com (DW00)


Use SPID from above to run the OS command to monitor the process:

#> prstat -m -p 3571 -c 5


The output:

  PID USERNAME USR SYS TRP TFL DFL LCK SLP LAT VCX ICX SCL SIG PROCESS/LWPID
3571  oracle11 100 0.0 0.0 0.0 0.0 0.0 0.0 0.0   7  30  22   1 oracle/1
3571  oracle11 100 0.0 0.0 0.0 0.0 0.0 0.0 0.0   9  29  22   1 oracle/1
3571  oracle11 100 0.0 0.0 0.0 0.0 0.0 0.0 0.0  15  51  30   1 oracle/1
3571  oracle11 100 0.0 0.0 0.0 0.0 0.0 0.0 0.0   8  29  21   1 oracle/1
3571  oracle11 100 0.0 0.0 0.0 0.0 0.0 0.0 0.0   4  32  30   1 oracle/1
3571  oracle11 100 0.0 0.0 0.0 0.0 0.0 0.0 0.0   0  33  13   1 oracle/1
3571  oracle11 100 0.0 0.0 0.0 0.0 0.0 0.0 0.0   0  39  15   1 oracle/1
3571  oracle11 100 0.0 0.0 0.0 0.0 0.0 0.0 0.0   0  35  13   1 oracle/1
3571  oracle11  99 0.8 0.0 0.0 0.0 0.0 0.0 0.0   0  37  16   1 oracle/1
3571  oracle11 100 0.0 0.0 0.0 0.0 0.0 0.0 0.0   4  36  17   1 oracle/1
3571  oracle11 100 0.0 0.0 0.0 0.0 0.0 0.0 0.0   0  29  15   1 oracle/1

shows that the process are continuously spending 100% of its time.

Compared to a DataPump export performed on other type of CPU:

;;;
Export: Release 11.2.0.3.0 - Production on Fri Nov 18 08:01:58 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=sr dumpfile=test.dmp logfile=test.log schemas=TEST parallel=32 cluster=n METRICS=Y CONTENT=METADATA_ONLY REUSE_DUMPFILES=yes
Processing object type SCHEMA_EXPORT/USER
     Completed 1 USER objects in 1 seconds
.
.
.
Processing object type SCHEMA_EXPORT/JOB
     Completed 5 JOB objects in 14 seconds
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /test.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 08:37:25

This shows that the same metadata export took 35 minutes.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms