DataPump Export (EXPDP) Is Slow When NLS_SORT Is Not Set To BINARY

(Doc ID 1905119.1)

Last updated on AUGUST 13, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1]
Information in this document applies to any platform.

Symptoms

DataPump export (EXPDP) of metadata is very slow.

When tracing the DataPump Worker process(es) by using:

An execution plan (as reported in trace file of DataPump Worker process) may be similar to:

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        4          4          4  TABLE ACCESS CLUSTER USER$ (cr=6 pr=0 pw=0 time=41 us)
        4          4          4   INDEX UNIQUE SCAN I_USER# (cr=2 pr=0 pw=0 time=14 us)(object id 11)
    11727      11727      11727  TABLE ACCESS FULL JAVASNM$ (cr=5958150 pr=0 pw=0 time=481424064 us)
    11727      11727      11727  TABLE ACCESS FULL JAVASNM$ (cr=5938368 pr=0 pw=0 time=480166767 us)
        5          5          5  FILTER  (cr=2061221 pr=0 pw=0 time=78173514 us)
    18961      18961      18961   VIEW  KU$_SYNONYM_VIEW (cr=11935692 pr=0 pw=0 time=960178486 us)
    18977      18977      18977    FILTER  (cr=39484 pr=0 pw=0 time=1208468 us)
    18977      18977      18977     NESTED LOOPS  (cr=39480 pr=0 pw=0 time=1110257 us)
    18977      18977      18977      NESTED LOOPS  (cr=20499 pr=0 pw=0 time=837963 us)
    18977      18977      18977       NESTED LOOPS  (cr=1518 pr=0 pw=0 time=521853 us)
    18977      18977      18977        TABLE ACCESS FULL SYN$ (cr=111 pr=0 pw=0 time=21317 us)
    18977      18977      18977        TABLE ACCESS BY INDEX ROWID OBJ$ (cr=1407 pr=0 pw=0 time=419667 us)
    18977      18977      18977         INDEX RANGE SCAN I_OBJ1 (cr=895 pr=0 pw=0 time=269995 us)(object id 36)
    18977      18977      18977       TABLE ACCESS CLUSTER USER$ (cr=18981 pr=0 pw=0 time=225390 us)
    18977      18977      18977        INDEX UNIQUE SCAN I_USER# (cr=4 pr=0 pw=0 time=79682 us)(object id 11)
    18977      18977      18977      TABLE ACCESS CLUSTER USER$ (cr=18981 pr=0 pw=0 time=218302 us)
    18977      18977      18977       INDEX UNIQUE SCAN I_USER# (cr=4 pr=0 pw=0 time=55440 us)(object id 11)
        1          1          1     NESTED LOOPS  (cr=4 pr=0 pw=0 time=53 us)
        2          2          2      FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=29 us)
        1          1          1      TABLE ACCESS CLUSTER USER$ (cr=4 pr=0 pw=0 time=21 us)
        2          2          2       INDEX UNIQUE SCAN I_USER# (cr=2 pr=0 pw=0 time=12 us)(object id 11)
        0          0          0     FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
        0          0          0     FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
        0          0          0     FILTER  (cr=0 pr=0 pw=0 time=0 us)
        0          0          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
        0          0          0       INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us)(object id 39)
        0          0          0       TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us)
        0          0          0        INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us)(object id 11)
        0          0          0      FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
        3          3          3   FILTER  (cr=13 pr=0 pw=0 time=390 us)
        3          3          3    TABLE ACCESS FULL KU$NOEXP_TAB (cr=13 pr=0 pw=0 time=377 us)
        0          0          0   TABLE ACCESS FULL KU$NOEXP_TAB (cr=4 pr=0 pw=0 time=244 us)

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