Pickleball spielen

A very useful 12c New Feature is the option to suppress the generation of redo during Data Pump import. I was talking about it during my recent 12c New Features class in Finland and like to share that info with the Oracle Community here. My usual demo user ADAM owned a table named BIG with one index on it. Both were in LOGGING mode when I exported them. The Data Pump export did not use any 12c New Feature and is not shown therefore.

SQL> select log_mode,force_logging from v$database;

LOG_MODE     FORCE_LOGGING
------------ ---------------------------------------
ARCHIVELOG   NO

SQL> select * from v$recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                             0                         0               0          0
BACKUP PIECE                             0                         0               0          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                            0                         0               0          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

The database is not in force logging mode – else the new Data Pump parameter would be ignored. Archive log mode was just turned on, therefore no archive log file yet. First I will show the redo generating way to import, which is the default. Afterwards the new feature for comparison.

SQL> host impdp adam/adam directory=DPDIR tables=big

Import: Release 12.1.0.1.0 - Production on Mon Jan 20 11:50:42 2014

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "ADAM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ADAM"."SYS_IMPORT_TABLE_01":  adam/******** directory=DPDIR tables=big
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ADAM"."BIG"                                660.1 MB 5942016 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "ADAM"."SYS_IMPORT_TABLE_01" successfully completed at Mon Jan 20 11:54:32 2014 elapsed 0 00:03:48

SQL> select * from v$recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                         13.47                         0              12          0
BACKUP PIECE                             0                         0               0          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                            0                         0               0          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

The conventional way with redo generation took almost 4 minutes and generated 12 archive logs – my online logs are 100 megabyte in size. Now let’s see the new feature:

SQL> drop table big purge;

Table dropped.

SQL> host impdp adam/adam directory=DPDIR tables=big transform=disable_archive_logging:y

Import: Release 12.1.0.1.0 - Production on Mon Jan 20 11:57:19 2014

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "ADAM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ADAM"."SYS_IMPORT_TABLE_01":  adam/******** directory=DPDIR tables=big transform=disable_archive_logging:y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ADAM"."BIG"                                660.1 MB 5942016 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "ADAM"."SYS_IMPORT_TABLE_01" successfully completed at Mon Jan 20 11:58:21 2014 elapsed 0 00:01:01

SQL> select * from v$recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                         13.47                         0              12          0
BACKUP PIECE                             0                         0               0          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                            0                         0               0          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

SQL> select table_name,logging from user_tables;

TABLE_NAME                                                   LOG
------------------------------------------------------------ ---
BIG                                                          YES

SQL> select index_name,logging from user_indexes;

INDEX_NAME                                                   LOG
------------------------------------------------------------ ---
BIG_IDX                                                      YES

Note that the segment attributes are not permanently changed to NOLOGGING by the Data Pump import.
The comparison shows a striking improvement in run time – because the 2nd run did not generate additional archive logs, we still see the same number as before the 2nd call.

Another option is to suppress redo generation only for the import of indexes, in my example with the command

impdp adam/adam directory=DPDIR tables=big transform=disable_archive_logging:y:index

That is a safer choice because indexes are always reproducible. Keep in mind that any NOLOGGING operation is a risk – that is the price to pay for the speed up.
As always: Don’t believe it, test it! 🙂