IMPDP creates indexes with parallel degree 1 during import

Last few days I’ve been playing with export and import of 750 GB database. During this whole trial and error process I’ve learned several things that were unfamiliar to me. In this blog post I want to share some of the stuff that I learnt.

I have Oracle 10.2.0.4 database on Solaris 64bit which I want to migrate to Oracle 11.1.0.7 on Linux 64bit. It is 750GB large database with mostly partitioned tables (compressed partitions).


Let's get to the subject of the post.

I won't talk much about export operation - just to mention that it is important to specify this bolded line in parameter file:
dumpfile=exportdb_%U.dmp
filesize=30G


This means that multiple 30GB sized dump files will be generated during export. It is important to specify %U expression or to manually define multiple dump files if you want to get the most from parallel processing during export and import.

In my case - after performing full export of the database I've created 20 dump files which is about 600GB of export data.


Now comes the tricky part - importing database to the new environment.

Data import was very fast as it finished in below 2,5 hours. During import server was fully utilized using all available resources in parallel processing. When index creation started utilization of the server dropped to about 10%.

Only one worker performed the entire work, while others were waiting - even with parallel option specified.

Then I found that this was the result of the very nasty bug:
“Bug 8604502 - IMPDP creates indexes with parallel degree 1 during import”

This issue is fixed in:

12.1 (Future Release)
11.2.0.2 (Server Patch Set)
11.2.0.1 Bundle Patch 7 for Exadata Database
11.1.0.7 Patch 24 on Windows Platforms

Great, I don’t have fix for this bug.
With only one worker creating indexes this import could last for days.


For a quick solution I’ve decided to perform import in two steps. Firstly I will import everything but indexes and in next step I will import just indexes.

Data import again lasted below 2,5 hours and it finished without any problems fully utilizing the server.

For index creation I’ve generated CREATE INDEX script using SQLFILE option in IMPDP.

*parameter file*
userid=xxx/yyy
sqlfile=createidx.sql
dumpfile=exportdb_%U.dmp
include=INDEX
directory=export_dir
schemas=(...)

As SQLFILE generates CREATE INDEX scripts with parallel degree set to 1 I’ve decided to manually edit script and replace parallel 1 to parallel 16.

Besides parallel 16 option, I’ve specified NOLOGGING option to restrict generation of large redo log. As I expected creation of large indexes I’ve allocated large enough temporary tablespace and specified higher pga_aggregate_target parameter.

Then executed script from sqlplus and monitored server utilization and waits.

Script finished in about 3 hours which was nice improvement.
Server was fully utilized - mostly using 16 processes for index creation.

Main waits were:
- WAITING direct path read
- WAITING direct path read temp
- WAITING direct path write temp

This waits were expected as temporary space was extensively used.


Later I’ve found nice article from great Oracle expert Randolf Geist - Concurrent Index Creation. As a solution for the same problem he wrote tool for concurrent index creation.
I plan to test this approach in my environment.