My Oracle Support Banner

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

Last updated on MARCH 27, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
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:
  <FILESYSTEM_PATCH>/datapump/expdp_test_utg8_01.dmp
  <FILESYSTEM_PATCH>/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

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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.