Check which part taking time in EXPDP by Metrics=y and logtime=y parameters
Metrics will give the number of times(seconds) spent on each part separate timing in the log file of expdp datapump. By adding a statement like “Completed 10 TABLE_STATISTICS objects in 0 seconds”
C:\WINDOWS\system32>expdp hr@pdb1 directory=dbbackup dumpfile=hr.dmp logfile=hr.log metrics=y reuse_dumpfiles=y
Export: Release 18.0.0.0.0 - Production on Sun Jan 23 12:24:04 2022
Version 18.4.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/********@pdb1 directory=dbbackup dumpfile=hr.dmp logfile=hr.log metrics=y reuse_dumpfiles=y
W-1 Startup took 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
W-1 Completed 20 INDEX_STATISTICS objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
W-1 Completed 10 TABLE_STATISTICS objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
W-1 Completed 1 MARKER objects in 6 seconds
W-1 Processing object type SCHEMA_EXPORT/USER
W-1 Completed 1 USER objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
W-1 Completed 8 SYSTEM_GRANT objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/ROLE_GRANT
W-1 Completed 2 ROLE_GRANT objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
W-1 Completed 1 DEFAULT_ROLE objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
W-1 Completed 1 TABLESPACE_QUOTA objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
W-1 Completed 1 PROCACT_SCHEMA objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
W-1 Completed 3 SEQUENCE objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
W-1 Completed 10 TABLE objects in 22 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/COMMENT
W-1 Completed 39 COMMENT objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
W-1 Completed 1 FUNCTION objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
W-1 Completed 2 PROCEDURE objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
W-1 Completed 1 ALTER_FUNCTION objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
W-1 Completed 2 ALTER_PROCEDURE objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/VIEW/VIEW
W-1 Completed 1 VIEW objects in 2 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
W-1 Completed 17 INDEX objects in 2 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
W-1 Completed 10 CONSTRAINT objects in 1 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
W-1 Completed 10 REF_CONSTRAINT objects in 1 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
W-1 Completed 2 TRIGGER objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/JOB
W-1 Completed 1 JOB objects in 0 seconds
W-1 . . exported "HR"."EMPLOYEES" 17.08 KB 107 rows in 0 seconds using direct_path
W-1 . . exported "HR"."TEST_BKP" 17.08 KB 107 rows in 0 seconds using direct_path
W-1 . . exported "HR"."LOCATIONS" 8.437 KB 23 rows in 0 seconds using direct_path
W-1 . . exported "HR"."JOB_HISTORY" 7.195 KB 10 rows in 0 seconds using direct_path
W-1 . . exported "HR"."JOBS" 7.109 KB 19 rows in 0 seconds using direct_path
W-1 . . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows in 0 seconds using direct_path
W-1 . . exported "HR"."COUNTRIES" 6.367 KB 25 rows in .9999999999999999999999999999999999999996 seconds using external_table
W-1 . . exported "HR"."REGIONS" 5.546 KB 4 rows in 0 seconds using direct_path
W-1 . . exported "HR"."EMPLOYEETEST" 0 KB 0 rows in 0 seconds using direct_path
W-1 . . exported "HR"."TEST" 5.5 KB 3 rows in 0 seconds using direct_path
W-1 Completed 10 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 1 seconds
W-1 Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
C:\backup\HR.DMP
LOGTIME parameter adds a timestamp to each line in your logfile. It will help to detect the performance of expdp datapump.
C:\WINDOWS\system32>expdp hr@pdb1 directory=dbbackup dumpfile=hr.dmp logfile=hr.log reuse_dumpfiles=y logtime=all
Export: Release 18.0.0.0.0 - Production on Sun Jan 23 12:37:45 2022
Version 18.4.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
23-JAN-22 12:37:51.636: Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/********@pdb1 directory=dbbackup dumpfile=hr.dmp logfile=hr.log reuse_dumpfiles=y logtime=all
23-JAN-22 12:37:55.863: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
23-JAN-22 12:37:56.103: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
23-JAN-22 12:37:56.394: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
23-JAN-22 12:38:02.441: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
23-JAN-22 12:38:02.643: Processing object type SCHEMA_EXPORT/USER
23-JAN-22 12:38:02.707: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
23-JAN-22 12:38:02.757: Processing object type SCHEMA_EXPORT/ROLE_GRANT
23-JAN-22 12:38:02.811: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
23-JAN-22 12:38:02.898: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
23-JAN-22 12:38:03.015: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
23-JAN-22 12:38:04.216: Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
23-JAN-22 12:38:15.324: Processing object type SCHEMA_EXPORT/TABLE/TABLE
23-JAN-22 12:38:26.342: Processing object type SCHEMA_EXPORT/TABLE/COMMENT
23-JAN-22 12:38:27.305: Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
23-JAN-22 12:38:27.507: Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
23-JAN-22 12:38:27.727: Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
23-JAN-22 12:38:27.802: Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
23-JAN-22 12:38:29.360: Processing object type SCHEMA_EXPORT/VIEW/VIEW
23-JAN-22 12:38:31.218: Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
23-JAN-22 12:38:32.096: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
23-JAN-22 12:38:33.083: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
23-JAN-22 12:38:33.251: Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
23-JAN-22 12:38:33.920: Processing object type SCHEMA_EXPORT/JOB
23-JAN-22 12:38:35.712: . . exported "HR"."EMPLOYEES" 17.08 KB 107 rows
23-JAN-22 12:38:35.765: . . exported "HR"."TEST_BKP" 17.08 KB 107 rows
23-JAN-22 12:38:35.828: . . exported "HR"."LOCATIONS" 8.437 KB 23 rows
23-JAN-22 12:38:35.895: . . exported "HR"."JOB_HISTORY" 7.195 KB 10 rows
23-JAN-22 12:38:35.952: . . exported "HR"."JOBS" 7.109 KB 19 rows
23-JAN-22 12:38:36.008: . . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
23-JAN-22 12:38:37.017: . . exported "HR"."COUNTRIES" 6.367 KB 25 rows
23-JAN-22 12:38:37.106: . . exported "HR"."REGIONS" 5.546 KB 4 rows
23-JAN-22 12:38:37.117: . . exported "HR"."EMPLOYEETEST" 0 KB 0 rows
23-JAN-22 12:38:37.172: . . exported "HR"."TEST" 5.5 KB 3 rows
23-JAN-22 12:38:38.792: Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
23-JAN-22 12:38:38.813: ******************************************************************************
23-JAN-22 12:38:38.814: Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
23-JAN-22 12:38:38.816: C:\backup\HR.DMP
23-JAN-22 12:38:38.901: Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Jan 23 12:38:38 2022 elapsed 0 00:00:51