ANBOB™

Indexes seem always created with parallel degree 1 during import as seen from a sqlfile. Even if the specified parallelism is greater than 1. like

1,   Version 11.1.0.7 11.2.0.1

# impdp  ....  parallel=16 sqlfile=xx.sql
The sql file shows content like:
CREATE INDEX "<SCHEMA_NAME>"."<INDEX_NAME>" ON
"<SCHEMA_NAME>"."<TABLE_NAME>" ("<COLUMN_NAME>")
...
PARALLEL 1 ; <<<<<

ALTER INDEX "<SCHEMA_NAME>"."<INDEX_NAME>" PARALLEL 16;

 
The database is version 11.2.0.2 or above where Bug 8604502 has been fixed.

2, Version 11.2.0.2 to 11.2.0.4 12.1

Indexes seem always created with parallel degree 1 during import as seen from a sqlfile.

The import job with SQLFILE parameter option cannot use multiple execution streams.
It always executed with parallel 1. Hence its showing the PARALLEL 1 in generated sqlfile and this is an expected behavior.

When you run the import with parallel value and without sqlfile option, then you can see the actual index creation statement with correct parallel value in DW(worker) traces.

3. Version 12.2.0.1 and later

From 12.2, DataPump import (impdp) will always use non-parallel index creation during import overriding parameter ‘parallel’ in command line or parameter file.

Oracle Development said

“General support for parallel import of most object type, including indexes, is a 12.2 feature, which led to study of parallel creation of individual indexes. What was found was that using parallel index creation was generally slower than non-parallel. That led to a decision to backport the change to not use parallel index creation.”

This behavior is only seen for index creation during impdp.

After creating the index, DataPump impdp job will use ‘ALTER INDEX … PARALLEL n’ to set index degree to the value of ‘parallel’ parameter issued in command line or parameter file.

4,  Parallel meatdata

The speed of Data Pump import is highly affected by the level of parallelism that can be achieved. Prior to 12.2, object metadata was imported serially. However, as of 12.2, parallelism is allowed for object metadata import of some objects, but only for specific situations. Parallel import of metadata is disallowed under the following circumstances:

When the TRANSPORT_TABLESPACES parameter is used
If the PARALLEL parameter is specified in conjunction with TRANSPORT_TABLESPACES, an ORA-39047 error results
When the TRANSPORT_DATAFILES parameter is used
If the PARALLEL parameter is specified in conjunction with TRANSPORT_DATAFILES, an ORA-39047 error results
When the TRANSPORTABLE parameter is used
Setting TRANSPORTABLE=ALWAYS and FULL=Y allows the PARALLEL parameter to be set in the same command line without error. However, there is some confusion here. This does not allow parallel import of object metadata. It only specifies parallel import of row data due the FULL=Y setting.
When the NETWORK_LINK parameter is used
A network mode job simultaneously exports from one database while importing into another, and code for the ordering and dependency work for metadata is not yet implemented for this case
When TRIGGERs, VIEWs, OBJECT_GRANTS, SEQUENCEs, CONSTRAINTs and REF_CONSTRAINTs are imported with parallel>1
Bug 27577866, Bug 28451919 and Bug 28539085 uncovered problems with importing these objects in parallel. The fix of first two bugs were superseded by the fix of Bug 28539085. The bug 28539085 was superseded by Bug 29613245
With Patch 29613245 in place, DataPump allows the following objects to be imported in parallel: SEQUENCE, VIEW, TRIGGER, OBJECT_GRANT, CONSTRAINT and REF_CONSTRAINT.

Stated conversely, we can only achieve parallelism when importing object metadata for situations when we are importing from a dump file and when we are not performing a transportable tablespace operation.

Implementing object metadata parallelism for TTS and NETWORK_LINK is being considered for future releases, but there are no firm plans for this as yet.

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.

A case of my previous migration ORACLE  db.  https://www.anbob.com/archives/1888.html

References

12.2 DataPump Import (IMPDP) Creates Index With Parallel=1 Even If Parallel>1 Is Set In Command Line or Parameter File (Doc ID 2296686.1)

打赏

微信扫一扫，打赏作者吧～