Compression Enhancements - Oracle 11g

By far, the compression capability is one of the greatest new features of the Data Pump utility in Oracle Database 11g.Oracle Data Pump takes the database exports to another level of usability by natively providing the compression option.

In Oracle Database 10g,you were able to compress only the metadata relative to your Data Pump extracts.In Oracle Database 11g, Oracle provides the mechanism to compress both data and metadata during the extract operation.To make full use of all these compression options, the compatible initialization parameter must be set to at least 11.1.

In the previous release of Oracle, DBAs created a Unix pipe and performed the export or Data Pump extract using the pipe as the target file.Behind the scenes, another Unix process compressed the output of the Unix pipe.

The following script demonstrates how DBAs performed export compression prior to Oracle Database 11g using the old exp utility:

if test -p $TARGET/exp/$ORACLE_SID.export.pipe
then
rm -f $TARGET/exp/$ORACLE_SID.export.pipe
mknod $TARGET/exp/$ORACLE_SID.export.pipe p
else
mknod $TARGET/exp/$ORACLE_SID.export.pipe p
fi
$NOHUP gzip
<$TARGET/exp/$ORACLE_SID.export.pipe
>$TARGET/exp/$ORACLE_SID.$TODAY.exp.gz &
echo "************************************
echo "${ORACLE_SID}'s Full database export started -- c"
date
exp "'/ AS SYSDBA'" buffer=31457280
full=y
compress=n
consistent=n
direct=n
rows=$YNFlag
statistics=none
file=$TARGET/exp/$ORACLE_SID.export.pipe
log=$LOGFILE 1> /dev/null 2>&1

Without the use of Unix pipes, DBAs often do not have enough space on disk to perform the export.Although it does not take a rocket scientist toimplement Unix pipes,it still involves a DBA with some in-depth Unix expertise.

As mentioned before,the metadata_only compression option was available in Oracle Database 10g.Oracle Database 11g adds the all and data_only options to the compression option.The available options for the COMPRESSION parameter are as follows:

compression={all | data_only | metadata_only | none}

The all option enables compression for the entire export operation,and the data_only option results in just the data being written to the dump file in compressed format.Let’s see how much space the COMPRESSION parameter can save us.

In the first example,we will perform a full database Data Pump export using the compression option and compare the size of the dump file with a non compressed Data Pump dump file.Here we perform a full database export using Data Pump with the compression=all option to a file named full.compress.dmp:

$ expdp full=yes userid="'/ as sysdba'"
dumpfile=dbadir:full.compress.dmp compression=all
Export: Release 11.1.0.6.0 - Production on Sunday, 23 September, 2007 9:13:50
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 "SYS"."SYS_EXPORT_FULL_01": full=yes userid='/******** AS SYSDBA'
dumpfile=dbadir:full.compress.dmp compression=all
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 238.5 MB
...
...

Even though the estimated size from Data Pump is 238.5MB, the actual size of the dump file is a little less than 84MB, as you see here:

-rw-r----- 1 oracle oinstall 87973888 Sep 23 09:31 full.compress.dmp

Performing the same export without the compression option produces a file that is substantially larger.The following Data Pump export produces a file called full.dmp approximatele 248.6MB in size:

DBA11g > expdp full=yes userid="'/ as sysdba'"dumpfile=dbadir:full.dmp -rw-r----- 1 oracle oinstall 260665344 Sep 23 08:48 full.dmp

Comparing the file sizes, the noncompressed dump file is 248.6MB in size, while the compressed dump file is approximately 83.9MB in size.You clearly see that the compression option for Data Pump can save you a significant amount of space.

DBAs may wonder how Data Pump compression compares to compression utilities like gzip.Using the gzip utility, we compress the full.dmp file. The size of the full.dmp.gz compresses down to 72.99MB:

-rw-r----- 1 oracle oinstall 76539609 Sep 23 08:48 full.dmp.gz

You can see that the gzip compression performs slightly better than the Data Pump compression using the compression=all option.The compression difference is negligible as you consider the ease of use factor.In conclusion, you should replace Unix pipes with Data Pump compression.

Compression Enhancements

Let’s continue our example and perform a Data Pump export against a single table.In this example, the CUST table is created as an image of the CUSTOMER table in the SH schema.In addition, the primary key is dropped,and data is quadrupled to examine the level of compression that Oracle is capable of producing for a table with 222,000 rows. Here we will perform a tablelevel export of the SH.CUST table:

DBA11g1 > expdp tables=sh. cust userid=sh/sh123456 dumpfile=oratmp:cust.dmp
Export: Release 11.1.0.6.0 - Production on Sunday, 23 September, 2007 15:08:08
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 "SH"."SYS_EXPORT_TABLE_01": tables=sh.cust userid=sh/********
dumpfile=oratmp:cust.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 47 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SH"."CUST" 39.37 MB 222000 rows
Master table "SH"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
********************************************
Dump file set for SH.SYS_EXPORT_TABLE_01 is:
/tmp/cust.dmp
Job "SH"."SYS_EXPORT_TABLE_01" successfully completed at 15:09:33
rac1.dbaexpert.com:/home/oracle
DBA11g1 > ls -l /tmp/cust.dmp
-rw-r----- 1 oracle oinstall 41377792 Sep 23 15:09 /tmp/cust.dmp

The size of the noncompressed dump file is approximately 39.46MB. Now using the compression=all option, we produce a compressed dump file named cust.compress.dmp, as shown here:

$ expdp tables=sh.cust userid=sh/sh123456
dumpfile=oratmp:cust.compress. dmp compression=all
Export: Release 11.1.0.6.0 - Production on Sunday, 23 September, 2007 15:11:11
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 "SH"."SYS_EXPORT_TABLE_01": tables=sh.cust userid=sh/********
dumpfile=oratmp:cust.compress. dmp compression=all
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 47 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SH"."CUST" 9.101 MB 222000 rows
Master table "SH"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
**********************************************
Dump file set for SH.SYS_EXPORT_TABLE_01 is:
/tmp/cust.compress.dmp
Job "SH"."SYS_EXPORT_TABLE_01" successfully completed at 15:12:23

The dump file size is significantly reduced relative to the noncompressed Data Pump export at approximately 9.13MB:

-rw-r----- 1 oracle oinstall 9576448 Sep 23 15:12 /tmp/cust.compress.dmp

Comparing the two files, the noncompressed dump file is 39.46MB in size,and the compressed dump file is 9.13MB in size.The gzipped version of the cust.dmp file produces a file that is 8.96MB in size:

-rw-r----- 1 oracle oinstall 9392929 Sep 23 15:09 cust.dmp.gz

Again, the compression difference between Data Pump compression and gzip is negligible. Let’s perform a Data Pump export using the compression=data_only option. Using our same table as before, we will create a dump file called cust.compress.data.dmp:

$ expdp tables=sh.cust userid=sh/sh123456
dumpfile=oratmp:cust.compress.data. dmp compression=data_only
Export: Release 11.1.0.6.0 - Production on Sunday, 23 September, 2007 15:29:17
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 "SH"."SYS_EXPORT_TABLE_01": tables=sh. cust userid=sh/********
dumpfile=oratmp:cust.compress.data. dmp compression=data_only
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 47 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SH"."CUST" 9.101 MB 222000 rows
Master table "SH"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
*************************************************
Dump file set for SH.SYS_EXPORT_TABLE_01 is:
/tmp/cust.compress.data.dmp
Job "SH"."SYS_EXPORT_TABLE_01" successfully completed at 15:30:26

You can see that the data_only option produces a file that is about 9.14MB in size, as shown here:

-rw-r----- 1 oracle oinstall 9588736 Sep 23 15:30 cust.compress.data.dmp

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 11g Topics