Oracle DBA by Example. Code samples, Scripts, Reference.: expdp estimate size for exported segments

==============================

General

==============================

Getting estimation for the size of exported segments before running expdp command.

Note, that when doing estimate, no dmp file is generated.

==============================

Estimate size for export file.

==============================

Syntax for getting estimate:

expdp user/pass@orainst directory=IG_EXP_DIR LOGFILE=test_exp.log estimate=BLOCKS ESTIMATE_ONLY=Y

expdp user/pass@orainst directory=IG_EXP_DIR LOGFILE=test_exp.log estimate=STATISTICS ESTIMATE_ONLY=Y

ESTIMATE_ONLY

A parameter that tells expdp process to skip actual export, and only to estimate the size.

Values: Y/N

Default=N

ESTIMATE

Method to estimate the size of a segment.

Values: BLOCKS/STATISTICS

Default: BLOCKS. Number of segment blocks * block size.

==============================

The Test

==============================

Same schema was tested with expdp estimate only
A. No up to date statistics, estimate=BLOCKS
B. No up to date statistics, estimate=STATISTICS
C. Gather DBMS_STATS for main three tables in the system
D. Up to date statistics, estimate=BLOCKS
E. Up to date statistics, estimate=STATISTICS

==============================

The Results

==============================

A. No up to date statistics, estimate=BLOCKS

my_user@my_server:~/workarea/ora_exp>% less test_exp.log

;;;

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

Starting "user"."SYS_EXPORT_SCHEMA_01": user/********@igt directory=IG_EXP_DIR LOGFILE=test_exp.log estimate=BLOCKS ESTIMATE_ONLY=Y

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. estimated "user"."IPN_SUBSCRIBER_USAGE_B" 2.187 GB

. estimated "user"."SGA_SUBSCRIBER_SFI" 2.127 GB

. estimated "user"."CLOVER_SUBSCRIBER_USAGE" 1.781 GB

. estimated "user"."SGA_W_IPN_SUBSCRIBER" 608 MB

. estimated "user"."SUBSCRIBER_USAGE_INFO" 424 MB

. estimated "user"."TAP_TEMP" 256 MB

B. No up to date statistics, estimate=STATISTICS

Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 23 October, 2018 18:06:53

;;;

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

Starting "user"."SYS_EXPORT_SCHEMA_01": user/********@igt directory=IG_EXP_DIR LOGFILE=test_exp_stats.log estimate=STATISTICS ESTIMATE_ONLY=Y

Estimate in progress using STATISTICS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. estimated "user"."SGA_W_IPN_SUBSCRIBER" 554.4 MB

. estimated "user"."CLOVER_SUBSCRIBER_USAGE" 341.5 MB

. estimated "user"."IPN_SUBSCRIBER_USAGE_B" 227.5 MB

. estimated "user"."SUBSCRIBER_USAGE_INFO" 71.95 MB

. estimated "user"."IPN_W_PNLS" 27.16 MB

. estimated "user"."SGA_SUBSCRIBER_COMMENTS" 27.01 MB

. estimated "user"."DEBUG_IPN_USAGE_VALUES" 7.867 MB

. estimated "user"."TAP_TEMP" 7.188 MB

C. Gather DBMS_STATS for main three tables in the system

BEGIN
DBMS_STATS.unlock_table_stats('user', 'SGA_W_IPN_SUBSCRIBER');
DBMS_STATS.gather_table_stats('user', 'SGA_W_IPN_SUBSCRIBER', estimate_percent => 100);
DBMS_STATS.lock_table_stats('user', 'SGA_W_IPN_SUBSCRIBER');
END;
/

PL/SQL procedure successfully completed.

BEGIN
DBMS_STATS.unlock_table_stats('user', 'CLOVER_SUBSCRIBER_USAGE');
DBMS_STATS.gather_table_stats('user', 'CLOVER_SUBSCRIBER_USAGE', estimate_percent => 100);
DBMS_STATS.lock_table_stats('user', 'CLOVER_SUBSCRIBER_USAGE');
END;
/

PL/SQL procedure successfully completed.

BEGIN
DBMS_STATS.unlock_table_stats('user', 'IPN_SUBSCRIBER_USAGE_B');
DBMS_STATS.gather_table_stats('user', 'IPN_SUBSCRIBER_USAGE_B', estimate_percent => 50);
DBMS_STATS.lock_table_stats('user', 'IPN_SUBSCRIBER_USAGE_B');
END;

/

PL/SQL procedure successfully completed.

D. Up to date statistics, estimate=BLOCKS

;;;
Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 23 October, 2018 19:11:42

Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "user"."SYS_EXPORT_SCHEMA_01": user/********@igt directory=IG_EXP_DIR LOGFILE=test_exp_blocks_correct.log estimate=BLOCKS ESTIMATE_ONLY=Y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "user"."IPN_SUBSCRIBER_USAGE_B" 2.187 GB
. estimated "user"."SGA_SUBSCRIBER_SFI" 2.127 GB
. estimated "user"."CLOVER_SUBSCRIBER_USAGE" 1.781 GB
. estimated "user"."SGA_W_IPN_SUBSCRIBER" 608 MB
. estimated "user"."SUBSCRIBER_USAGE_INFO" 424 MB
. estimated "user"."TAP_TEMP" 256 MB

E. Up to date statistics, estimate=STATISTICS

Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 23 October, 2018 18:50:41

;;;

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

Starting "user"."SYS_EXPORT_SCHEMA_01": user/********@igt directory=IG_EXP_DIR LOGFILE=test_exp_stats_correct.log estimate=STATISTICS ESTIMATE_ONLY=Y

Estimate in progress using STATISTICS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. estimated "user"."IPN_SUBSCRIBER_USAGE_B" 1.531 GB

. estimated "user"."CLOVER_SUBSCRIBER_USAGE" 1.053 GB

. estimated "user"."SGA_W_IPN_SUBSCRIBER" 448.0 MB

. estimated "user"."SUBSCRIBER_USAGE_INFO" 71.95 MB

. estimated "user"."IPN_W_PNLS" 27.16 MB

. estimated "user"."SGA_SUBSCRIBER_COMMENTS" 27.01 MB

. estimated "user"."DEBUG_IPN_USAGE_VALUES" 7.867 MB

. estimated "user"."TAP_TEMP" 7.188 MB

==============================

Conclusion
==============================

1. Gathering table stats has change dramatically the estimated size when using STATISTICS method.

2. STATISTICS method estimate was significantly lower than the BLOCKS estimate.

When Using Blocks Estimate:

Before statistics gathering:

.  estimated "user"."IPN_SUBSCRIBER_USAGE_B"   2.187 GB

.  estimated "user"."SGA_SUBSCRIBER_SFI"       2.127 GB

.  estimated "user"."CLOVER_SUBSCRIBER_USAGE"  1.781 GB

After statistics gathering:

. estimated "user"."IPN_SUBSCRIBER_USAGE_B" 2.187 GB

. estimated "user"."SGA_SUBSCRIBER_SFI" 2.127 GB

. estimated "user"."CLOVER_SUBSCRIBER_USAGE" 1.781 GB

When Using Statistics Estimate:

Before statistics gathering:

. estimated "user"."SGA_W_IPN_SUBSCRIBER" 554.4 MB

. estimated "user"."CLOVER_SUBSCRIBER_USAGE" 341.5 MB

.  estimated "user"."IPN_SUBSCRIBER_USAGE_B"   227.5 MB

After statistics gathering:

. estimated "user"."IPN_SUBSCRIBER_USAGE_B" 1.531 GB

. estimated "user"."CLOVER_SUBSCRIBER_USAGE" 1.053 GB

. estimated "user"."SGA_W_IPN_SUBSCRIBER" 448.0 MB

Why was STATISTICS method estimate was significantly lower than the BLOCKS estimate?

That might be due to fragmentation of the table blocks in the OLTP system.

While BLOCKS represent the actual size of the segments, the STATISTICS represent the optimal size, when segments are not fragmented.

Oracle DBA by Example. Code samples, Scripts, Reference.

Pages

Tuesday, October 23, 2018

expdp estimate size for exported segments

No comments:

Post a Comment