Thursday, April 30, 2009

How to get timing details on data pump processed objects

We can get the number of objects processed and timing information needed to process object types in data pump jobs.

We can achieve this goal by using the undocumented parameter METRICS. By setting parameter METRICS to y we can get timing details.

An example is given below.

E:\Documents and Settings\Arju>expdp schemas=arju userid=arju/a dumpfile=arju_30_04.dmp logfile=arju_20_04.log metrics=y

Export: Release 10.2.0.1.0 - Production on Friday, 01 May, 2009 7:24:07

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "ARJU"."SYS_EXPORT_SCHEMA_03": schemas=arju userid=arju/******** dumpfile=arju_30_04.dmp logfile=arju_20_04.lo
g metrics=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1024 KB
Processing object type SCHEMA_EXPORT/USER
Completed 1 USER objects in 0 seconds
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Completed 1 SYSTEM_GRANT objects in 1 seconds
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Completed 1 ROLE_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Completed 1 DEFAULT_ROLE objects in 0 seconds
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Completed 1 PROCACT_SCHEMA objects in 0 seconds
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Completed 1 SEQUENCE objects in 3 seconds
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Completed 11 TABLE objects in 4 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Completed 9 INDEX objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Completed 8 CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Completed 10 INDEX_STATISTICS objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Completed 2 COMMENT objects in 1 seconds
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Completed 3 VIEW objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Completed 11 TABLE_STATISTICS objects in 0 seconds
. . exported "ARJU"."SYS_EXPORT_SCHEMA_02" 214.3 KB 1125 rows
. . exported "ARJU"."SYS_EXPORT_SCHEMA_01" 31.55 KB 12 rows
. . exported "ARJU"."AUTHOR" 5.835 KB 14 rows
. . exported "ARJU"."BOOKAUTHOR" 5.609 KB 20 rows
. . exported "ARJU"."BOOKS" 7.781 KB 14 rows
. . exported "ARJU"."BOOK_CUSTOMER" 8.234 KB 21 rows
. . exported "ARJU"."BOOK_ORDER" 8.398 KB 21 rows
. . exported "ARJU"."ORDERITEMS" 6.742 KB 32 rows
. . exported "ARJU"."PROMOTION" 5.710 KB 4 rows
. . exported "ARJU"."PUBLISHER" 6.265 KB 8 rows
. . exported "ARJU"."T" 4.914 KB 1 rows
Master table "ARJU"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_SCHEMA_03 is:
E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\DPDUMP\ARJU_30_04.DMP
Job "ARJU"."SYS_EXPORT_SCHEMA_03" successfully completed at 07:24:41

Note that with usage of additional undocumented parameter METRICS=Y, the following output is displayed in the logfile as well as on the screen.
Completed %n %s objects in %n seconds
Related Documents
http://arjudba.blogspot.com/2009/04/data-pump-process-architecture-master.html

No comments:

Post a Comment