What You See Is What You Get Element

Earlier Data Migrations with Oracle Database 12c Data Pump

by Paul Guerin

Export to a significantly smaller dump file using the advanced compression capability of Oracle Database 12c Data Pump.

Published November 2017


Using the export and import operations, the Data Pump feature of Oracle Database 12c is an excellent tool for data migrations. By default, the export operation will create a compact dump file that can be modestly smaller than the source table segment. Also the dump file can be created locally or transported across a network to a remote location. For transporting a dump file to a remote location use the DIRECTORY and NETWORK_LINK clauses.

Regardless of whether you are creating the dump file locally or to a remote location, it is often advantageous to create a significantly smaller dump file. A significantly smaller dump file can result in the export operation finishing much earlier. Over a network, a smaller dump file will also increase network traffic less than a larger file. To export to a significantly smaller dump file, Data Pump offers advanced compression.

With Data Pump advanced compression, it's possible to create a significantly smaller dump file from three compression types. Using the COMPRESSION_ALGORITHM clause, these different types of compression can be chosen:

  • BASIC (default)
  • LOW
  • MEDIUM
  • HIGH

So including basic compression, there are now four different types of compression.

The question is how effective are the different types of compression? How much smaller can the dump file be, and how much additional CPU time is required?

Data Pump Export with Basic Compression

Let's start with a Data Pump export of a single table using basic compression:

expdp system parfile=exportdp.par compression=ALL compression_algorithm=BASIC

The output log for an export with basic compression resembles the following:

..
Starting "SYSTEM"."EXPDP":  system/******** parfile=exportdp.par COMPRESSION=ALL COMPRESSION_ALGORITHM=BASIC
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.134 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "DATAPUMPTEST"."TEST_COMPRESSION"           19.59 MB 11000000 rows
Master table "SYSTEM"."EXPDP" successfully loaded/unloaded
******************************************************************************
..
Job "SYSTEM"."EXPDP" successfully completed at Sun Aug 13 19:52:31 2017 elapsed 0 00:00:52

The output log shows that from a source table estimated to be 1.134 GB, the dump file becomes 19.59 MB in size.

The elapsed time was 52 seconds; however, the CPU time was about 60 seconds.

Data Pump Export with Advanced Compression

Now, if we use a Data Pump export with the COMPRESSION_ALGORITHM=LOW clause, the output log is as follows:

..
Starting "SYSTEM"."EXPDP":  system/******** parfile=exportdp.par COMPRESSION=ALL COMPRESSION_ALGORITHM=LOW
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.134 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "DATAPUMPTEST"."TEST_COMPRESSION"           26.16 MB 11000000 rows
Master table "SYSTEM"."EXPDP" successfully loaded/unloaded
******************************************************************************
..
Job "SYSTEM"."EXPDP" successfully completed at Sun Aug 27 19:15:58 2017 elapsed 0 00:02:48

Using LOW compression, the dump file is still very small at 26.16 MB.

Next, let's perform a Data Pump export featuring the COMPRESSION_ALGORITHM=MEDIUM clause:

..
Starting "SYSTEM"."EXPDP":  system/******** parfile=exportdp.par COMPRESSION=ALL COMPRESSION_ALGORITHM=MEDIUM
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.134 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "DATAPUMPTEST"."TEST_COMPRESSION"           6.524 MB 11000000 rows
Master table "SYSTEM"."EXPDP" successfully loaded/unloaded
******************************************************************************
..
Job "SYSTEM"."EXPDP" successfully completed at Sun Aug 13 20:03:07 2017 elapsed 0 00:00:46

As expected, an export with MEDIUM compression results in a smaller dump file and becomes just 6.524 MB in size.

The CPU time is about the same as for a BASIC compression export.

The last export will be performed using the COMPRESSION_ALGORITHM=HIGH clause:

..
Starting "SYSTEM"."EXPDP":  system/******** parfile=exportdp.par COMPRESSION=ALL COMPRESSION_ALGORITHM=HIGH
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.134 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "DATAPUMPTEST"."TEST_COMPRESSION"           3.299 MB 11000000 rows
Master table "SYSTEM"."EXPDP" successfully loaded/unloaded
******************************************************************************
..
Job "SYSTEM"."EXPDP" successfully completed at Sun Aug 13 20:08:13 2017 elapsed 0 00:00:53

As shown in the output log, for the same table, the dump file created using HIGH compression is 3.299 MB.

Elapsed time is 53 seconds, but the CPU time is about 70 seconds.

Summary

The Oracle Database 12c Data Pump feature permits four possible compression levels when exporting to a dump file.

However using the highest level compression (COMPRESSION_ALGORITHM=HIGH clause) results in a dump file of just 3.299 MB from a 1.134 GB table!

The CPU trade-off of 70 seconds was a little higher than when using basic compression, which took 60 seconds.

The advanced compression feature of Data Pump creates exceptionally small dump files, with a modest increase in CPU time, and this results in the highest levels of data migration performance.

See Also

About the Author

Paul Guerin is an international consultant who specializes in Oracle Database performance. He is based at a global delivery center in Southeast Asia, but has clients from Australia, Europe, Asia, and the United States. Moreover, he has presented at some of the world's leading Oracle conferences, including Oracle OpenWorld 2013. His work has also been featured in the Independent Oracle Users Group (IOUG) Tips and Best Practices Booklet of 2015 and via several database community articles of the My Oracle Support Community. He is an Oracle Certified Professional DBA and continues to be a participant of the Oracle ACE program.