Friday, April 15, 2011

Estimate Parameter in Data Pump Export

ESTIMATE  parameter  of data pump  export specify the method that Export will use to estimate how much disk space  each  table in the export  job will  consume (in bytes)  before  performing actual data pump export operation .
The ESTIMATE parameter can take two parameters. Either BLOCKS (default) or STATISTICSThe meaning of these two parameter values are specified below.

BLOCKS : The estimate is calculated by multiplying the number of database blocks used by the target objects with the appropriate block sizes.
STATISTICS : The estimate is calculated using statistics for each table. So to be accurate you must analyze table recently.

Note that the outcome specified by ESTIMATE=BLOCKS is far away from the size of the actual dumpfile. In fact, ESTIMATE=BLOCKS method generates more inaccurate result from dump file size when,

a) The table was created with a much bigger initial extent size than was needed for the actual table data.
b) Many rows have been deleted from the table, or a very small percentage of each block is used.
The outcome generated by ESTIMATE=STATISTICS is most accurate to dump file size if recently table is analyzed.

Below is an example shown both in case of ESTIMATE=STATISTICS and ESTIMATE=BLOCKS. In both cases data pump export dump file is generated after estimation of dump file.

C:\>expdp system/ramtech@noida directory=dpump  schemas=hr logfile=hrlog11.log dumpfile=hr.dmp ESTIMATE=BLOCKS
Export: Release 11.1.0.6.0 - Production on Friday, 15 April, 2011 19:26:02
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@noida directory=dpump schemas=hr logfile=hrlog11.log ESTIMATE=BLOCKS
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "HR"."COUNTRIES"                                64 KB
.  estimated "HR"."DEPARTMENTS"                          64 KB
.  estimated "HR"."EMPLOYEES"                               64 KB
.  estimated "HR"."JOBS"                                             64 KB
.  estimated "HR"."JOB_HISTORY"                             64 KB
.  estimated "HR"."LOCATIONS"                                64 KB
.  estimated "HR"."REGIONS"                                     64 KB
Total estimation using BLOCKS method: 448 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "HR"."COUNTRIES"                                6.375 KB      25 rows
. . exported "HR"."DEPARTMENTS"                          7.015 KB      27 rows
. . exported "HR"."EMPLOYEES"                              16.80 KB     107 rows
. . exported "HR"."JOBS"                                             6.984 KB      19 rows
. . exported "HR"."JOB_HISTORY"                            7.054 KB      10 rows
. . exported "HR"."LOCATIONS"                               8.273 KB      23 rows
. . exported "HR"."REGIONS"                                     5.484 KB       4 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  D:\DPUMP\EXPDAT.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:28:16

Now we will export the schemas by using ESTIMATE=STATISTICS

C:\>expdp system/ramtech@noida directory=dpump  schemas=hr dumpfile=hr1.dmp logfile=hrlog111.log ESTIMATE=STATISTICS
 Export: Release 11.1.0.6.0 - Production on Friday, 15 April, 2011 19:31:15
 Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@noida directory=dpump schemas=hr dumpfile=hr1.dmp logfile=hrlog111.log ESTIMATE=STATISTICS
Estimate in progress using STATISTICS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "HR"."EMPLOYEES"                           15.91 KB
.  estimated "HR"."LOCATIONS"                             8.034 KB
.  estimated "HR"."JOB_HISTORY"                          6.861 KB
.  estimated "HR"."JOBS"                                           6.795 KB
.  estimated "HR"."DEPARTMENTS"                        6.710 KB
.  estimated "HR"."COUNTRIES"                              6.150 KB
.  estimated "HR"."REGIONS"                                   5.488 KB
Total estimation using STATISTICS method: 55.95 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "HR"."EMPLOYEES"                            16.80 KB     107 rows
. . exported "HR"."LOCATIONS"                            8.273 KB      23 rows
. . exported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
. . exported "HR"."JOBS"                                 6.984 KB      19 rows
. . exported "HR"."DEPARTMENTS"                          7.015 KB      27 rows
. . exported "HR"."COUNTRIES"                            6.375 KB      25 rows
. . exported "HR"."REGIONS"                              5.484 KB       4 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  D:\DPUMP\HR1.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:33:24

Using ESTIMATE=BLOCKS, before data pump export size is shown as 448 KB and using ESTIMATE=STATISTICS, before data pump export size is shown as 55.95 KB and my actual dump file size was 408KB which is away from estimation using ESTIMATE=BLOCKS as difference is 448-408=40 KB. In later case difference is 448-55.95=392.05KB

Note that if a table involves LOBs, the dump file size may vary as ESTIMATE does not take LOB size into consideration.

Enjoy      J J J


No comments: