DataPump Export (EXPDP) Is Slow When NLS_SORT Is Not Set To BINARY
(Doc ID 1905119.1)
Last updated on MARCH 29, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1]Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |