EXPDP Very Slow On Indexes (SYS.KU$_INDEX_VIEW) After Upgrade To 11.2.0.4 (Doc ID 1990632.1)

Last updated on AUGUST 29, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.

Symptoms

Expdp is very slow at the stage of exporting indexes.

The export log may show like below:

Export: Release 11.2.0.4.0 - Production on Fri Feb 27 14:23:35 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
Starting "SYS"."EXPDP_TEST_UTG8":  /******** AS SYSDBA parfile=expdp_test_utg8.par
....
....
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
    Completed 1 INDEX objects in 4735 seconds                                                <<<<====  Possible slowness here.
    Completed 6536 TABLE_EXPORT/TABLE/TABLE_DATA objects in 70 seconds
Master table "SYS"."EXPDP_TEST_UTG8" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.EXPDP_TEST_UTG8 is:
 /backup/SRPFUTG8/datapump/expdp_test_utg8_01.dmp
 /backup/SRPFUTG8/datapump/expdp_test_utg8_02.dmp
Job "SYS"."EXPDP_TEST_UTG8" successfully completed at Fri Feb 27 15:44:35 2015 elapsed 0 01:20:54

 

Information from the AWR report shows information like:

Begin Snap: 121518 27-Feb-15 14:32:16 34 2.6
End Snap: 121520 27-Feb-15 15:30:18 35 2.6

SQL ordered by Elapsed Time

Elapsed Time (s) Executions  Elapsed Time per Exec (s)  %Total %CPU %IO SQL Id SQL Module SQL Text
3,476.43 0   127.70 53.26 3.51 494bpyrm6ha5w    BEGIN SYS.KUPW$WORKER.MAIN('E...
3,475.05 0   127.65 53.26 3.52 as23kn94d5wr1 Data Pump Worker  SELECT /*+all_rows*/ SYS_XMLGE...     <<<<==== Datapump SQL consuming more time.
1,703.02 1 1,703.02 62.56 29.22 51.19 dthgax46968au    INSERT /*+ BYPASS_RECURSIVE_CH...
372.14 1 372.14 13.67 47.98 20.74 1v0smfdtt3rjh    DECLARE job BINARY_INTEGER


Complete List of SQL Text:

as23kn94d5wr1 SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('INDEX_T', '7')), KU$.OBJ_NUM , KU$.ANC_OBJ.NAME , KU$.ANC_OBJ.OWNER_NAME , KU$.ANC_OBJ.TYPE_NAME , KU$.SCHEMA_OBJ.NAME , KU$.SCHEMA_OBJ.NAME , 'INDEX' , KU$.PROPERTY , KU$.SCHEMA_OBJ.OWNER_NAME , KU$.TS_NAME , to_char(KU$.TYPE_NUM) , decode(cardinality(KU$.COL_LIST), 0, '1', '0') FROM SYS.KU$_INDEX_VIEW KU$ WHERE NOT KU$.FOR_PKOID=1 AND NOT KU$.FOR_REFPAR=1 AND NOT (KU$.TYPE_NUM=1 AND KU$.INTCOLS=1 AND KU$.OID_OR_SETID!=0) AND NOT BITAND(KU$.BASE_OBJ.FLAGS, 128)!=0 AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS, 4)=4 AND KU$.SCHEMA_OBJ.OWNER_NAME IN (SELECT UNIQUE object_schema FROM "SYS"."EXPDP_TEST_UTG8" WHERE process_order = -55 AND duplicate BETWEEN 1 AND 1) AND KU$.TYPE_NUM NOT IN (2, 4, 8, 9) AND KU$.BASE_OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) AND BITAND(KU$.PROPERTY, 16)!=16  ------> SQL accessing SYS.KU$_INDEX_VIEW that consumes more time.


The slowness is while accessing SYS.KU$_INDEX_VIEW but relevant bugs for this issue, e.g. 13844935, 13914808 and 13714302, are already fixed in 11.2.0.4.

Changes

Upgrade from 11.2.0.3 to 11.2.0.4

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