Analyze time of expdp datapump by METRICS=Y and LOGTIME=ALL parameters

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.