Archive for the ‘Datapump’ Category

Test for compression ratio for using compression ALL in datapump exports at 11G, this is a very simple test of a small test schema with 2 tables.

Set-up Env

TEST ENV testdb1 SQL> col DIRECTORY_PATH format a20

TEST ENV testdb1 SQL> col DIRECTORY_PATH format a50
TEST ENV testdb1 SQL> select directory_name, directory_path from dba_directories;

DIRECTORY_NAME DIRECTORY_PATH
—————————— ————————————————–
DATA_PUMP_DIR /wload/test1/app/oracle/admin/testdb1/dpdump/
ORACLE_OCM_CONFIG_DIR /wload/test1/app/oracle/product/11.2.0.2/db11g/ccr
/state

Test 1 – Compression =ALL

expdp system/******* directory=DATA_PUMP_DIR dumpfile=test_schema_compressed.dmp schemas=test compression=ALL PARALLEL=2 logfile=test_schema_compressed.log
Export: Release 11.2.0.2.0 – Production on Wed Mar 28 11:30:44 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, Oracle Label Security and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″: system/******** directory=DATA_PUMP_DIR dumpfile=test_schema_compressed.dmp schemas=test compression=ALL PARALLEL=2 logfile=test_schema_compressed.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 399 MB
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported “TEST”.”ARRAY2″ 116.7 MB 1000000 rows
. . exported “TEST”.”ARRAY1″ 116.7 MB 1000000 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/wload/test1/app/oracle/admin/testdb1/dpdump/test_schema_compressed.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 11:32:28

Test 2 – No compression

expdp system/****** directory=DATA_PUMP_DIR dumpfile=test_schema_not_compressed.dmp schemas=test PARALLEL=2 logfile=test_schema_not_compressed.log
Export: Release 11.2.0.2.0 – Production on Wed Mar 28 11:33:23 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, Oracle Label Security and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″: system/******** directory=DATA_PUMP_DIR dumpfile=test_schema_not_compressed.dmp schemas=test PARALLEL=2 logfile=test_schema_not_compressed.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 399 MB
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported “TEST”.”ARRAY2″ 164.0 MB 1000000 rows
. . exported “TEST”.”ARRAY1″ 164.0 MB 1000000 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/wload/test1/app/oracle/admin/testdb1/dpdump/test_schema_not_compressed.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 11:34:01

Results

So when checking the export dump file size the compression ratio is not so great be interesting to see how this affects larger dumps.

$ du -sm *.dmp
234 test_schema_compressed.dmp
329 test_schema_not_compressed.dmp