Slow expdp.
The process hangs at step Processing INDEXES
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
View SYS.KU$_CONSTRAINT_VIEW is having more than 1,000,000 rows, and the SQL accessing that view is extremely slow.
Investigation
Following This Oracle Technote did not help:
Expdp Performance Issue - Datapump expdp is slow and hanging on queries against datapump dictionary views ( SYS.KU$ views) (Doc ID 2704705.1)
It suggested to run tuning query, and follow suggestion
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '3kq263ugt7drn');
SELECT DBMS_SQLTUNE.report_tuning_task( '3kq263ugt7drn') FROM DUAL;
The suggestion was to gather stats for table 'KU$NOEXP_TAB'
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'KU$NOEXP_TAB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
But that did not make a difference.
Solution
Following This Oracle Technote helped:
Bug 31050896 - Datapump Export Slow on Constraints When Using Privileged User (Doc ID 31050896.8)
Instead of running expdp as schema owner, run expdp as system with SCHEMAS tag
Description
If export is started by a privileged user (SYSTEM or a user with
DATAPUMP_EXP_FULL_DATABASE), the expdp takes long time exporting constraints.
The slow export operation is observed during unload of:
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
for the following Data Pump internal query:
SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$),
XMLFORMAT.createFormat2('CONSTRAINT_T', '7')), 0 ,
NVL(KU$.CON1.IND.ANC_OBJ.NAME, KU$.BASE_OBJ.NAME) ,KU$.BASE_OBJ.OWNER_NAME ,
KU$.BASE_OBJ.TYPE_NAME ,KU$.NAME ,KU$.NAME ,'CONSTRAINT' ,KU$.OWNER_NAME
FROM
SYS.KU$_CONSTRAINT_VIEW KU$ WHERE ...
Workaround
If the export is started by the user that owns the constraints the issue should be avoided.
