DataPump Import (IMPDP) Is Very Slow for DOMAIN_INDEX
(Doc ID 1948129.1)
Last updated on JULY 02, 2023
Applies to:
Oracle Database Cloud Schema Service - Version N/A and laterOracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Backup Service - Version N/A 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
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."<COLUMN_NAME>" from
"<SCHEMA_NAME>"."<TABLE_NAME>" 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."<COLUMN_NAME>" from
"<SCHEMA_NAME>"."<TABLE_NAME>" 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)
......
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."<COLUMN_NAME>" from
"<SCHEMA_NAME>"."<TABLE_NAME>" 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."<COLUMN_NAME>" from
"<SCHEMA_NAME>"."<TABLE_NAME>" 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.
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |