Skip to Main Content
  • Questions
  • Export time doubled after adding encryption

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Arun.

Asked: October 23, 2017 - 7:29 pm UTC

Last updated: October 25, 2017 - 3:44 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Recently, we added encryption directives to database exports. We noticed that the export time doubled. In your experience, is this expected behavior?

Database1:
Before 54 mins
After 1 hr 40 mins

Database2:
Before 30 mins
After 58 mins

Database3:
Before 14 mins
After 27 mins

Database4:
Before 25 mins
After 42 mins

Database5:
Before 48 mins
After 1 hr 19 mins

These are the lines we added to the export parameter file:

ENCRYPTION_PASSWORD=<password>
ENCRYPTION_MODE=DUAL
ENCRYPTION_ALGORITHM=aes256
ENCRYPTION=ALL

Thanks,
Arun

and Connor said...

Encryption is a CPU-intensive process. If your CPU is a limiting factor, then it obviously has a larger impact on the overall time.

For examaple, for me (on a smaller scale):

C:\Users\hamcdc>expdp dumpfile=blah3 directory=temp tables=TEST0922

Export: Release 12.2.0.1.0 - Production on Tue Oct 24 11:36:39 2017

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

Username: *****

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "MCDONAC"."SYS_EXPORT_TABLE_01":  mcdonac/******** dumpfile=blah3 directory=temp tables=TEST0922
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "MCDONAC"."TEST0922"                        852.0 MB 8388608 rows
Master table "MCDONAC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MCDONAC.SYS_EXPORT_TABLE_01 is:
  C:\TEMP\BLAH3.DMP
Job "MCDONAC"."SYS_EXPORT_TABLE_01" successfully completed at Tue Oct 24 11:36:56 2017 elapsed 0 00:00:13



C:\Users\hamcdc>expdp dumpfile=blah2 directory=temp tables=TEST0922 ENCRYPTION_PASSWORD=****ENCRYPTION_ALGORITHM=aes256 ENCRYPTION=ALL

Export: Release 12.2.0.1.0 - Production on Tue Oct 24 11:35:50 2017

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

Username: ****

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "MCDONAC"."SYS_EXPORT_TABLE_01":  mcdonac/******** dumpfile=blah2 directory=temp tables=TEST0922 ENCRYPTION_PASSWORD=******** ENCRYPTION_ALGORITHM=aes256 ENCRYPTION=ALL
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "MCDONAC"."TEST0922"                        852.0 MB 8388608 rows
Master table "MCDONAC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MCDONAC.SYS_EXPORT_TABLE_01 is:
  C:\TEMP\BLAH2.DMP
Job "MCDONAC"."SYS_EXPORT_TABLE_01" successfully completed at Tue Oct 24 11:36:11 2017 elapsed 0 00:00:18


But nothing is "free". If you are running single stream *and* you have spare CPU capacity, you could mitigate this with parallel options.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, October 24, 2017 - 1:08 pm UTC

The databases are on Exadata two node RAC. Each RAC node has 14 CPUs. These are non-production databases, so there is not much activity. I checked in OEM and CPU is not a limiting factor during export. Even in your test case, the time increase is less than 25%. In my case, it is close to 100%. Is this kind of time increase expected with plenty of resources available?
Connor McDonald
October 25, 2017 - 3:44 am UTC

You could have 1000 cpu's... if you are running your Datapump in a single session, then you only get to use 1.

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.