DataPump Import (IMPDP) Is Very Slow for DOMAIN_INDEX (Doc ID 1948129.1)

Last updated on APRIL 26, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.

Symptoms

- Import of DOMAIN_INDEX/INDEX is eating up around 33 hours:

Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
    Completed 6 INDEX objects in 118746 seconds

- Import using EXCLUDE=DOMAIN_INDEX/INDEX takes only 1.5 hours to complete.

- From the DW trace file, the slowness is with creating multiple domain indexes in parallel:

SQL ID:
Plan Hash: 422828966
select column_value
from
 table(cast(ctxsys.drvparx.ParallelPopuIndex(cursor(select /*+
  DYNAMIC_SAMPLING(0) PARALLEL(base 3) FULL(base) NOCACHE(base) */ rowid,
  NULL, NULL, NULL, 'N', base."DOCUMENT_CONTENT"    from
  "ODB"."TRAXDOC_DETAIL" base), :idxownid, :idxid, :idxown, :idxname,:ixpname,:popstate) as sys.odcivarchar2list))

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 73     (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  VIEW  (cr=4009848 pr=1086343 pw=19613 time=506086920 us)
      1   COLLECTION ITERATOR PICKLER FETCH PARALLELPOPUINDEX (cr=4009848 pr=1086343 pw=19613 time=506086903 us)
      0    TABLE ACCESS FULL TRAXDOC_DETAIL (cr=0 pr=0 pw=0 time=0 us)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0          0
Execute      1      0.00       0.00          0        162          0          0
Fetch        2  17413.33  107858.76     927718    3835011      61095          1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4  17413.33  107858.77     927718    3835173      61095          1

SQL ID:
Plan Hash: 422828974
select column_value
from
 table(cast(ctxsys.drvparx.ParallelPopuIndex(cursor(select /*+
  DYNAMIC_SAMPLING(0) PARALLEL(base 3) FULL(base) NOCACHE(base) */ rowid,
  NULL, NULL, NULL, 'N', base."TASK_CARD_TEXT_BLOB"    from
  "ODB"."TASK_CARD_ITEM_REV" base), :idxownid, :idxid, :idxown, :idxname, :ixpname,:popstate) as sys.odcivarchar2list))

call     count       cpu    elapsed       disk      query    current        
rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0          0
Execute      1      0.00       0.00          0        162          0          0
Fetch        2   1165.13    7755.92          0       1031       2103          1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4   1165.13    7755.92          0       1193       2103          1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 73     (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  VIEW  (cr=7114 pr=2 pw=0 time=422828696 us)
      1   COLLECTION ITERATOR PICKLER FETCH PARALLELPOPUINDEX (cr=7114 pr=2 pw=0 time=422828677 us)
      0    TABLE ACCESS FULL TASK_CARD_ITEM_REV (cr=0 pr=0 pw=0 time=0 us)
......

 
- The manual creation of only one domain index takes 13 hours by itself, with or without parallel. Since there are multiple domain indexes created in parallel, the DataPump import job takes about 33 hours to create the indexes.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms