slow datapump export in 10.2.0.4

Export Slow After Applying 10.2.0.4 Patchset On Top Of 10.2.0.3 [ID 783835.1]

Symptoms

Export is slow after applying patchset 10.2.0.4.0 on top of 10.2.0.3.

An export (exp) that previously took approximately 8 hours now takes 2 days after applying the patchset 10.2.0.4.0.

Cause

The cause of slow performance is incorrect statistics.

You have to narrow down where the possible bottlenecks are by tracing the exp process:

-- Determine SPID of export process
select distinct (p.spid), s.sid, s.serial#
from   v$process p, v$session s, v$mystat m
where  s.PADDR = p.ADDR and lower (s.program) like '%exp%';

-- connect to the process as sysdba
connect / as sysdba
oradebug setospid xxxx <-- use SPID here
oradebug unlimit

-- Create process state dumps
oradebug dump errorstack 3
... wait 30 seconds
oradebug dump errorstack 3
... wait 30 seconds
oradebug dump errorstack 3

This generates a trace file in your user_dump_dest (from sqlplus: show parameter user_dump_dest).

When reviewing the file you saw the following:

Current SQL statement for this session:
SELECT CNO, CNAME, TYPE, CONDITION, CONDLENGTH, ENABLED, DEFER, INAME, IDXSYSGEND FROM SYS.EXU8CON WHERE OBJID = :1 AND TYPE IN (1, 2, 3, 7, 11, 12, 14, 15, 16, 17) ORDER BY CNO

Also, from the RDA, the AWR report seems to indicate that there is a lot of CPU time spent on
querying constraints.  Specifically, the following query:

SELECT CNO, CNAME, TYPE, CONDITION, CONDLENGTH, ENABLED, DEFER, INAME, IDXSYSGEND FROM SYS.EXU8CON WHERE OBJID = :1 AND TYPE IN (1, 2, 3, 7, 11, 12, 14, 15, 16, 17) ORDER BY CNO

You can use the trace file generated from the "oradebug dump errorstack 3" to get the bind variable for the above query.  Search the trace file for "Current cursor": Current cursor: 57, pgadep: 0 ...and then search on "Cursor#57"

Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000000 frm=00 csi=00 siz=24 off=0
kxsbbbfp=800003ffefcf2ec0 bln=22 avl=04 flg=05
value=535520 <---- This is your Bind Variable's value.

Now you can determine how long it is taking to complete that query:

SQL> set pages 1000 lines 132
SQL> set autotrace on
SQL> SELECT CNO, CNAME, TYPE, CONDITION, CONDLENGTH, ENABLED, DEFER, INAME, IDXSYSGEND FROM SYS.EXU8CON WHERE OBJID = 535520 AND TYPE IN (1, 2, 3, 7, 11, 12, 14, 15, 16, 17) ORDER BY CNO ;

That should tell you how long it takes to to complete that query.

Solution

Workarounds:

1.  Export with constraints=n

Or:

2. Gather dictionary stats again and run the query again.

SQL> exec dbms_stats.gather_dictionary_stats;

SQL> set pages 1000 lines 132
SQL> set autotrace on
SQL> SELECT CNO, CNAME, TYPE, CONDITION, CONDLENGTH, ENABLED, DEFER, INAME, IDXSYSGEND
     FROM SYS.EXU8CON
     WHERE OBJID = 535520 AND TYPE IN (1, 2, 3, 7, 11, 12, 14, 15, 16, 17)
     ORDER BY CNO ;

Compare times for query to run before and after statistics are gathered.