Loading The Data Through DataPump Import (IMPDP) Takes Long Time (Doc ID 1993547.1)

Last updated on JULY 17, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 17-Jul-2017***

Symptoms

- While importing through IMPDP, the table_data load becomes really slow and takes long time.

- The import log file generated for an import done with metrics=y, clearly shows that the metadata is loaded very fast and the biggest import time is spent on the data load process.

- An AWR report collected during the import execution shows the time is spent on :

SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM FROM SYS.KU$_10_1_PHTABLE_VIEW KU$ WHERE NOT BITAND(KU$.SCHEMA_OBJ.FLAGS, 128)!=0 AND KU$.SCHEMA_OBJ.OWNER_NAME=:SCHEMA1 AND KU$.SCHEMA_OBJ.NAME=:NAME2;

SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM FROM SYS.KU$_10_1_HTABLE_VIEW KU$ WHERE NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 AND KU$.SCHEMA_OBJ.OWNER_NAME=:SCHEMA1 AND  KU$.SCHEMA_OBJ.NAME=:NAME2;

and this information is also seen in DM and DW traces:

SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), 0
FROM
SYS.KU$_10_1_HTABLE_VIEW KU$ WHERE NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0
 AND  KU$.SCHEMA_OBJ.OWNER_NAME=:SCHEMA1 AND  KU$.SCHEMA_OBJ.NAME=:NAME2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      123     13.41      13.42          0          0          0           0
Execute    123  21783.47   21782.58          0   56273850          0           0
Fetch      123      1.27       1.27          0       9305          0         123
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      369  21798.17   21797.29          0   56283155          0         123

 

Changes

The values for optimizer_features_enable and compatible parameters don't match with the database version.
For this particular case, the database version is 11.2.0.4, but optimizer_features_enable=10.1.0.4 and compatible=10.1.0.2.0.

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