Slow expdp in oracle 19.0 and higher

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.