This discussion is archived
7 Replies Latest reply: Jan 11, 2013 10:49 AM by TSharma-Oracle RSS

Performance issue when trying to execute DBMS_METADATA.GET_DDL of a table

982500 Newbie
Currently Being Moderated
Hello.

I have a database with lots of partitioned tables and indexes and when I try to get the DDL of a partitioned table the query runs for hours and does not end.
I tried do gather data dictionary statistics but it had no impact on performance.

Can anybody help me find what's causing this performance problem?

Information about the enviorment:

Oracle version:
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

DDL query:
select dbms_metadata.get_ddl('TABLE', 'TABLE1') from dual;

On the "Top Activity" of the Database Control console, this is the query that's running:
SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM
FROM SYS.KU$_PHTABLE_VIEW KU$
WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 AND KU$.SCHEMA_OBJ.NAME=:NAME1 AND KU$.SCHEMA_OBJ.OWNER_NAME=:SCHEMA2;

Edited by: Krulikowski on Jan 11, 2013 10:44 AM
  • 1. Re: Performance issue when trying to execute DBMS_METADATA.GET_DDL of a table
    982500 Newbie
    Currently Being Moderated
    And the execution plan of this query:

    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 2202002960

    --------------------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 3 | 861 | | 9 (0)| 00:00:01 |
    | 1 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |* 2 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 3 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |* 4 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    |* 5 | COUNT STOPKEY | | | | | | |
    | 6 | NESTED LOOPS | | 1 | 130 | | 6 (0)| 00:00:01 |
    | 7 | NESTED LOOPS | | 1 | 111 | | 5 (0)| 00:00:01 |
    |* 8 | TABLE ACCESS FULL | SECOBJ$ | 1 | 10 | | 2 (0)| 00:00:01 |
    | 9 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 101 | | 3 (0)| 00:00:01 |
    |* 10 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 2 (0)| 00:00:01 |
    | 11 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |* 12 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 13 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |* 14 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    |* 15 | COUNT STOPKEY | | | | | | |
    | 16 | NESTED LOOPS | | 1 | 142 | | 7 (0)| 00:00:01 |
    | 17 | NESTED LOOPS | | 1 | 123 | | 6 (0)| 00:00:01 |
    | 18 | NESTED LOOPS | | 1 | 22 | | 3 (0)| 00:00:01 |
    |* 19 | TABLE ACCESS FULL | SECOBJ$ | 1 | 10 | | 2 (0)| 00:00:01 |
    | 20 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 12 | | 1 (0)| 00:00:01 |
    |* 21 | INDEX UNIQUE SCAN | I_IND1 | 1 | | | 0 (0)| 00:00:01 |
    | 22 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 101 | | 3 (0)| 00:00:01 |
    |* 23 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 2 (0)| 00:00:01 |
    | 24 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |* 25 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 26 | TABLE ACCESS CLUSTER | SEG$ | 1 | 70 | | 3 (0)| 00:00:01 |
    |* 27 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | | 2 (0)| 00:00:01 |
    | 28 | TABLE ACCESS BY INDEX ROWID | DEFERRED_STG$ | 1 | 28 | | 2 (0)| 00:00:01 |
    |* 29 | INDEX UNIQUE SCAN | I_DEFERRED_STG1 | 1 | | | 1 (0)| 00:00:01 |
    | 30 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |* 31 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 32 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 47 | | 2 (0)| 00:00:01 |
    |* 33 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | | 1 (0)| 00:00:01 |
    | 34 | VIEW | | 1 | 2114 | | 5 (20)| 00:00:01 |
    | 35 | SORT ORDER BY | | 1 | 55 | | 5 (20)| 00:00:01 |
    | 36 | NESTED LOOPS | | 1 | 55 | | 4 (0)| 00:00:01 |
    | 37 | NESTED LOOPS | | 1 | 45 | | 3 (0)| 00:00:01 |
    |* 38 | TABLE ACCESS CLUSTER | TAB$ | 1 | 8 | | 2 (0)| 00:00:01 |
    |* 39 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |* 40 | TABLE ACCESS CLUSTER | COL$ | 1 | 37 | | 1 (0)| 00:00:01 |
    |* 41 | TABLE ACCESS CLUSTER | COL$ | 1 | 10 | | 1 (0)| 00:00:01 |
    |* 42 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    | 43 | NESTED LOOPS | | 1 | 135 | | 6 (0)| 00:00:01 |
    | 44 | NESTED LOOPS | | 1 | 116 | | 5 (0)| 00:00:01 |
    |* 45 | TABLE ACCESS CLUSTER | TAB$ | 1 | 15 | | 2 (0)| 00:00:01 |
    |* 46 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    | 47 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 101 | | 3 (0)| 00:00:01 |
    |* 48 | INDEX RANGE SCAN | I_OBJ1 | 25 | | | 2 (0)| 00:00:01 |
    | 49 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |* 50 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 51 | NESTED LOOPS | | 1 | 150 | | 1 (0)| 00:00:01 |
    | 52 | TABLE ACCESS BY INDEX ROWID | SYS_FBA_TRACKEDTABLES | 1 | 8 | | 1 (0)| 00:00:01 |
    |* 53 | INDEX UNIQUE SCAN | SYS_C001432 | 1 | | | 0 (0)| 00:00:01 |
    | 54 | TABLE ACCESS BY INDEX ROWID | SYS_FBA_FA | 1 | 142 | | 0 (0)| 00:00:01 |
    |* 55 | INDEX UNIQUE SCAN | SYS_C001418 | 1 | | | 0 (0)| 00:00:01 |
    | 56 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
    | 57 | TABLE ACCESS BY INDEX ROWID | ENC$ | 1 | 26 | | 0 (0)| 00:00:01 |
    |* 58 | INDEX RANGE SCAN | ENC_IDX | 1 | | | 0 (0)| 00:00:01 |
    | 59 | TABLE ACCESS BY INDEX ROWID | ENC$ | 1 | 26 | | 0 (0)| 00:00:01 |
    |* 60 | INDEX RANGE SCAN | ENC_IDX | 1 | | | 0 (0)| 00:00:01 |
    | 61 | NESTED LOOPS | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 62 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |* 63 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 64 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |* 65 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 66 | NESTED LOOPS | | | | | | |
    | 67 | NESTED LOOPS | | 1 | 85 | | 5 (0)| 00:00:01 |
    | 68 | NESTED LOOPS | | 1 | 50 | | 4 (0)| 00:00:01 |
    | 69 | NESTED LOOPS | | 1 | 25 | | 3 (0)| 00:00:01 |
    |* 70 | INDEX UNIQUE SCAN | I_COL3 | 1 | 10 | | 2 (0)| 00:00:01 |
    |* 71 | TABLE ACCESS CLUSTER | CCOL$ | 1 | 15 | | 1 (0)| 00:00:01 |
    |* 72 | INDEX UNIQUE SCAN | I_COBJ# | 1 | | | 0 (0)| 00:00:01 |
    | 73 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 25 | | 1 (0)| 00:00:01 |
    |* 74 | INDEX UNIQUE SCAN | I_CON2 | 1 | | | 0 (0)| 00:00:01 |
    |* 75 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | | 0 (0)| 00:00:01 |
    |* 76 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 35 | | 1 (0)| 00:00:01 |
    | 77 | VIEW | | 16 | 86864 | | 3 (34)| 00:00:01 |
    | 78 | SORT ORDER BY | | 16 | 896 | | 3 (34)| 00:00:01 |
    | 79 | TABLE ACCESS CLUSTER | COL$ | 16 | 896 | | 2 (0)| 00:00:01 |
    |* 80 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    | 81 | NESTED LOOPS | | | | | | |
    | 82 | NESTED LOOPS | | 1 | 60 | | 3 (0)| 00:00:01 |
    |* 83 | TABLE ACCESS CLUSTER | CDEF$ | 1 | 35 | | 2 (0)| 00:00:01 |
    |* 84 | INDEX UNIQUE SCAN | I_COBJ# | 1 | | | 1 (0)| 00:00:01 |
    |* 85 | INDEX UNIQUE SCAN | I_CON2 | 1 | | | 0 (0)| 00:00:01 |
    | 86 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 25 | | 1 (0)| 00:00:01 |
    | 87 | TABLE ACCESS CLUSTER | TAB$ | 1 | 13 | | 2 (0)| 00:00:01 |
    |* 88 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    | 89 | NESTED LOOPS | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 90 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |* 91 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 92 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |* 93 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    |* 94 | FILTER | | | | | | |
    | 95 | NESTED LOOPS | | 1 | 28 | | 4 (0)| 00:00:01 |
    | 96 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 15 | | 3 (0)| 00:00:01 |
    |* 97 | INDEX RANGE SCAN | I_CCOL1 | 1 | | | 2 (0)| 00:00:01 |
    |* 98 | TABLE ACCESS CLUSTER | COL$ | 1 | 13 | | 1 (0)| 00:00:01 |
    |* 99 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    | 100 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 47 | | 2 (0)| 00:00:01 |
    |*101 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | | 1 (0)| 00:00:01 |
    | 102 | NESTED LOOPS | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 103 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |*104 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 105 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*106 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 107 | NESTED LOOPS | | 1 | 105 | | 6 (0)| 00:00:01 |
    | 108 | NESTED LOOPS | | 1 | 85 | | 5 (0)| 00:00:01 |
    | 109 | NESTED LOOPS | | 1 | 77 | | 4 (0)| 00:00:01 |
    | 110 | NESTED LOOPS | | 1 | 56 | | 3 (0)| 00:00:01 |
    |*111 | TABLE ACCESS CLUSTER | COL$ | 1 | 39 | | 2 (0)| 00:00:01 |
    |*112 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*113 | TABLE ACCESS CLUSTER | CCOL$ | 1 | 17 | | 1 (0)| 00:00:01 |
    |*114 | INDEX UNIQUE SCAN | I_COBJ# | 1 | | | 0 (0)| 00:00:01 |
    |*115 | TABLE ACCESS CLUSTER | COLTYPE$ | 1 | 21 | | 1 (0)| 00:00:01 |
    |*116 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    |*117 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 8 | | 1 (0)| 00:00:01 |
    |*118 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | | 0 (0)| 00:00:01 |
    |*119 | TABLE ACCESS CLUSTER | COL$ | 1 | 20 | | 1 (0)| 00:00:01 |
    |*120 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    | 121 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 47 | | 2 (0)| 00:00:01 |
    |*122 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | | 1 (0)| 00:00:01 |
    | 123 | NESTED LOOPS | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 124 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |*125 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 126 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*127 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 128 | NESTED LOOPS | | 1 | 70 | | 3 (0)| 00:00:01 |
    |*129 | TABLE ACCESS CLUSTER | COL$ | 1 | 39 | | 2 (0)| 00:00:01 |
    |*130 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*131 | TABLE ACCESS CLUSTER | COL$ | 1 | 31 | | 1 (0)| 00:00:01 |
    |*132 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    | 133 | NESTED LOOPS | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 134 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |*135 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 136 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*137 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 138 | VIEW | | 1 | 102 | | 5 (20)| 00:00:01 |
    | 139 | SORT ORDER BY | | 1 | 68 | | 5 (20)| 00:00:01 |
    | 140 | NESTED LOOPS | | 1 | 68 | | 4 (0)| 00:00:01 |
    | 141 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 19 | | 3 (0)| 00:00:01 |
    |*142 | INDEX RANGE SCAN | I_CCOL1 | 1 | | | 2 (0)| 00:00:01 |
    |*143 | TABLE ACCESS CLUSTER | COL$ | 1 | 49 | | 1 (0)| 00:00:01 |
    |*144 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    | 145 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*146 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 147 | NESTED LOOPS | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 148 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |*149 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 150 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*151 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 152 | VIEW | | 1 | 1182 | | 6 (17)| 00:00:01 |
    | 153 | SORT ORDER BY | | 1 | 91 | | 6 (17)| 00:00:01 |
    | 154 | NESTED LOOPS | | 1 | 91 | | 5 (0)| 00:00:01 |
    | 155 | NESTED LOOPS | | 1 | 42 | | 4 (0)| 00:00:01 |
    | 156 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 10 | | 2 (0)| 00:00:01 |
    |*157 | INDEX UNIQUE SCAN | I_IND1 | 1 | | | 1 (0)| 00:00:01 |
    | 158 | TABLE ACCESS BY INDEX ROWID | ICOL$ | 1 | 32 | | 2 (0)| 00:00:01 |
    |*159 | INDEX RANGE SCAN | I_ICOL1 | 1 | | | 1 (0)| 00:00:01 |
    |*160 | TABLE ACCESS CLUSTER | COL$ | 1 | 49 | | 1 (0)| 00:00:01 |
    | 161 | TABLE ACCESS CLUSTER | SEG$ | 1 | 70 | | 3 (0)| 00:00:01 |
    |*162 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | | 2 (0)| 00:00:01 |
    | 163 | TABLE ACCESS BY INDEX ROWID | DEFERRED_STG$ | 1 | 28 | | 2 (0)| 00:00:01 |
    |*164 | INDEX UNIQUE SCAN | I_DEFERRED_STG1 | 1 | | | 1 (0)| 00:00:01 |
    | 165 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*166 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 167 | NESTED LOOPS | | 1 | 120 | | 5 (0)| 00:00:01 |
    | 168 | D TABLE ACCESS BY INDEX ROWI | OBJ$ | 1 | 101 | | 4 (0)| 00:00:01 |
    |*169 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 170 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
  • 2. Re: Performance issue when trying to execute DBMS_METADATA.GET_DDL of a table
    982500 Newbie
    Currently Being Moderated
    |*171 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 172 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*173 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 174 | NESTED LOOPS | | 1 | 120 | | 5 (0)| 00:00:01 |
    | 175 | WID TABLE ACCESS BY INDEX RO | OBJ$ | 1 | 101 | | 4 (0)| 00:00:01 |
    |*176 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 177 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*178 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 179 | OWID TABLE ACCESS BY INDEX R | OBJ$ | 1 | 30 | | 4 (0)| 00:00:01 |
    |*180 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 181 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*182 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    |*183 | INDEX RANGE SCAN | I_OBJ1 | 1 | 10 | | 3 (0)| 00:00:01 |
    | 184 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*185 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 186 | NESTED LOOPS | | 1 | 130 | | 6 (0)| 00:00:01 |
    | 187 | NESTED LOOPS | | 1 | 111 | | 5 (0)| 00:00:01 |
    |*188 | TABLE ACCESS FULL | SECOBJ$ | 1 | 10 | | 2 (0)| 00:00:01 |
    | 189 | EX ROWID TABLE ACCESS BY IND | OBJ$ | 1 | 101 | | 3 (0)| 00:00:01 |
    |*190 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 2 (0)| 00:00:01 |
    | 191 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*192 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 193 | VIEW | | 2 | 94 | | 25 (0)| 00:00:01 |
    | 194 | UNION-ALL | | | | | | |
    | 195 | NESTED LOOPS | | 1 | 136 | | 12 (0)| 00:00:01 |
    | 196 | NESTED LOOPS | | 1 | 117 | | 11 (0)| 00:00:01 |
    | 197 | NESTED LOOPS | | 1 | 83 | | 8 (0)| 00:00:01 |
    | 198 | TESIAN MERGE JOIN CAR | | 1 | 64 | | 7 (0)| 00:00:01 |
    | 199 | NESTED LOOPS | | 1 | 30 | | 4 (0)| 00:00:01 |
    |*200 | BY INDEX ROWID TABLE ACCESS | IND$ | 1 | 16 | | 2 (0)| 00:00:01 |
    |*201 | E SCAN INDEX UNIQU | I_IND1 | 1 | | | 1 (0)| 00:00:01 |
    |*202 | FULL TABLE ACCESS | INDTYPES$ | 1 | 14 | | 2 (0)| 00:00:01 |
    | 203 | BUFFER SORT | | 1 | 34 | | 5 (0)| 00:00:01 |
    | 204 | BY INDEX ROWID TABLE ACCESS | OBJ$ | 1 | 34 | | 3 (0)| 00:00:01 |
    |*205 | SCAN INDEX RANGE | I_OBJ1 | 1 | | | 2 (0)| 00:00:01 |
    | 206 | LUSTER TABLE ACCESS C | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*207 | SCAN INDEX UNIQUE | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 208 | INDEX ROWID TABLE ACCESS BY | OBJ$ | 1 | 34 | | 3 (0)| 00:00:01 |
    |*209 | AN INDEX RANGE SC | I_OBJ1 | 1 | | | 2 (0)| 00:00:01 |
    | 210 | STER TABLE ACCESS CLU | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*211 | AN INDEX UNIQUE SC | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 212 | NESTED LOOPS | | 1 | 148 | | 13 (0)| 00:00:01 |
    | 213 | NESTED LOOPS | | 1 | 129 | | 12 (0)| 00:00:01 |
    | 214 | NESTED LOOPS | | 1 | 95 | | 9 (0)| 00:00:01 |
    | 215 | TESIAN MERGE JOIN CAR | | 1 | 76 | | 8 (0)| 00:00:01 |
    | 216 | NESTED LOOPS | | 1 | 42 | | 5 (0)| 00:00:01 |
    | 217 | NESTED LOOPS | | 1 | 28 | | 3 (0)| 00:00:01 |
    |*218 | S BY INDEX ROWID TABLE ACCES | PARTOBJ$ | 1 | 12 | | 2 (0)| 00:00:01 |
    |*219 | UE SCAN INDEX UNIQ | I_PARTOBJ$ | 1 | | | 1 (0)| 00:00:01 |
    |*220 | S BY INDEX ROWID TABLE ACCES | IND$ | 1 | 16 | | 1 (0)| 00:00:01 |
    |*221 | UE SCAN INDEX UNIQ | I_IND1 | 1 | | | 0 (0)| 00:00:01 |
    |*222 | FULL TABLE ACCESS | INDTYPES$ | 1 | 14 | | 2 (0)| 00:00:01 |
    | 223 | BUFFER SORT | | 1 | 34 | | 6 (0)| 00:00:01 |
    | 224 | BY INDEX ROWID TABLE ACCESS | OBJ$ | 1 | 34 | | 3 (0)| 00:00:01 |
    |*225 | SCAN INDEX RANGE | I_OBJ1 | 1 | | | 2 (0)| 00:00:01 |
    | 226 | LUSTER TABLE ACCESS C | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*227 | SCAN INDEX UNIQUE | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 228 | INDEX ROWID TABLE ACCESS BY | OBJ$ | 1 | 34 | | 3 (0)| 00:00:01 |
    |*229 | AN INDEX RANGE SC | I_OBJ1 | 1 | | | 2 (0)| 00:00:01 |
    | 230 | STER TABLE ACCESS CLU | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*231 | AN INDEX UNIQUE SC | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 232 | VIEW | | 1 | 60 | | 10 (20)| 00:00:01 |
    | 233 | SORT GROUP BY | | 1 | 92 | | | |
    | 234 | CONCATENATION | | | | | | |
    | 235 | NESTED LOOPS | | 1 | 92 | | 4 (0)| 00:00:01 |
    | 236 | S NESTED LOOP | | 1 | 73 | | 3 (0)| 00:00:01 |
    | 237 | SS BY INDEX ROWID TABLE ACCE | JIJOIN$ | 1 | 39 | | 1 (0)| 00:00:01 |
    |*238 | GE SCAN INDEX RAN | I_JIJOIN$ | 1 | | | 1 (0)| 00:00:01 |
    | 239 | SS BY INDEX ROWID TABLE ACCE | OBJ$ | 1 | 34 | | 2 (0)| 00:00:01 |
    |*240 | GE SCAN INDEX RAN | I_OBJ1 | 1 | | | 2 (0)| 00:00:01 |
    | 241 | S CLUSTER TABLE ACCES | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*242 | UE SCAN INDEX UNIQ | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 243 | NESTED LOOPS | | 1 | 92 | | 4 (0)| 00:00:01 |
    | 244 | S NESTED LOOP | | 1 | 73 | | 3 (0)| 00:00:01 |
    | 245 | SS BY INDEX ROWID TABLE ACCE | JIJOIN$ | 1 | 39 | | 1 (0)| 00:00:01 |
    |*246 | GE SCAN INDEX RAN | I_JIJOIN$ | 1 | | | 1 (0)| 00:00:01 |
    | 247 | SS BY INDEX ROWID TABLE ACCE | OBJ$ | 1 | 34 | | 2 (0)| 00:00:01 |
    |*248 | GE SCAN INDEX RAN | I_OBJ1 | 1 | | | 2 (0)| 00:00:01 |
    | 249 | S CLUSTER TABLE ACCES | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*250 | UE SCAN INDEX UNIQ | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 251 | PS NESTED LOO | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 252 | ESS BY INDEX ROWID TABLE ACC | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |*253 | NGE SCAN INDEX RA | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 254 | ESS CLUSTER TABLE ACC | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*255 | IQUE SCAN INDEX UN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    |*256 | SS CLUSTER TABLE ACCE | COL$ | 1 | 49 | | 2 (0)| 00:00:01 |
    |*257 | QUE SCAN INDEX UNI | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    | 258 | OPS NESTED LO | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 259 | CESS BY INDEX ROWID TABLE AC | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |*260 | ANGE SCAN INDEX R | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 261 | CESS CLUSTER TABLE AC | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*262 | NIQUE SCAN INDEX U | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    |*263 | ESS CLUSTER TABLE ACC | COL$ | 1 | 49 | | 2 (0)| 00:00:01 |
    |*264 | IQUE SCAN INDEX UN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    | 265 | VIEW | | 1 | 185 | | 0 (0)| 00:00:01 |
    |*266 | ESS BY INDEX ROWID TABLE ACC | JIJOIN$ | 1 | 117 | | 0 (0)| 00:00:01 |
    | 267 | LL SCAN INDEX FU | I2_JIJOIN$ | 1 | | | 0 (0)| 00:00:01 |
    | 268 | CESS CLUSTER TABLE AC | TS$ | 1 | 18 | | 1 (0)| 00:00:01 |
    |*269 | NIQUE SCAN INDEX U | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 270 | CCESS CLUSTER TABLE A | TS$ | 1 | 8 | | 1 (0)| 00:00:01 |
    |*271 | UNIQUE SCAN INDEX | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 272 | VIEW | | 1 | 56 | | 4 (25)| 00:00:01 |
    | 273 | ORDER BY SORT | | 1 | 57 | | 4 (25)| 00:00:01 |
    | 274 | ED LOOPS NEST | | 1 | 57 | | 3 (0)| 00:00:01 |
    |*275 | LE ACCESS FULL TAB | INSERT_TSN_LIST$ | 1 | 39 | | 2 (0)| 00:00:01 |
    | 276 | LE ACCESS CLUSTER TAB | TS$ | 1 | 18 | | 1 (0)| 00:00:01 |
    |*277 | DEX UNIQUE SCAN IN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 278 | STED LOOPS NE | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 279 | ABLE ACCESS BY INDEX ROWID T | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |*280 |.INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 281 | ABLE ACCESS CLUSTER T | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*282 |.INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 283 | EW VI | | 1 | 86 | | 6 (17)| 00:00:01 |
    | 284 | ORT ORDER BY S | | 1 | 76 | | 6 (17)| 00:00:01 |
    | 285 |.NESTED LOOPS | | 1 | 76 | | 5 (0)| 00:00:01 |
    | 286 |. NESTED LOOPS | | 1 | 27 | | 4 (0)| 00:00:01 |
    | 287 |. TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 12 | | 2 (0)| 00:00:01 |
    |*288 |. INDEX UNIQUE SCAN | I_IND1 | 1 | | | 1 (0)| 00:00:01 |
    | 289 |. TABLE ACCESS BY INDEX ROWID | PARTCOL$ | 1 | 15 | | 2 (0)| 00:00:01 |
    |*290 |. INDEX RANGE SCAN | I_PARTCOL$ | 1 | | | 1 (0)| 00:00:01 |
    |*291 |. TABLE ACCESS CLUSTER | COL$ | 1 | 49 | | 1 (0)| 00:00:01 |
    | 292 |. NESTED LOOPS | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 293 |. TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |*294 |. INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 295 |. TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*296 |. INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 297 |. VIEW | | 1 | 86 | | 5 (20)| 00:00:01 |
    | 298 |. SORT ORDER BY | | 1 | 76 | | 5 (20)| 00:00:01 |
    | 299 |. NESTED LOOPS | | 1 | 76 | | 4 (0)| 00:00:01 |
    | 300 |. NESTED LOOPS | | 1 | 27 | | 3 (0)| 00:00:01 |
    | 301 |. TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 12 | | 2 (0)| 00:00:01 |
    |*302 |. INDEX UNIQUE SCAN | I_IND1 | 1 | | | 1 (0)| 00:00:01 |
    | 303 |. TABLE ACCESS BY INDEX ROWID | SUBPARTCOL$ | 1 | 15 | | 1 (0)| 00:00:01 |
    |*304 |. INDEX RANGE SCAN | I_SUBPARTCOL$ | 1 | | | 0 (0)| 00:00:01 |
    |*305 |. TABLE ACCESS CLUSTER | COL$ | 1 | 49 | | 1 (0)| 00:00:01 |
    | 306 |. TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*307 |. INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 308 |. NESTED LOOPS | | 1 | 120 | | 5 (0)| 00:00:01 |
    | 309 |. TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 101 | | 4 (0)| 00:00:01 |
    |*310 |. INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 311 |. TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*312 |. INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 313 |. TABLE ACCESS BY INDEX ROWID | DEFERRED_STG$ | 1 | 28 | | 2 (0)| 00:00:01 |
    |*314 |. INDEX UNIQUE SCAN | I_DEFERRED_STG1 | 1 | | | 1 (0)| 00:00:01 |
    | 315 |. TABLE ACCESS CLUSTER | SEG$ | 1 | 70 | | 3 (0)| 00:00:01 |
    |*316 |. INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | | 2 (0)| 00:00:01 |
    | 317 |. NESTED LOOPS | | | | | | |
    | 318 |. NESTED LOOPS | | 195 | 10530 | | 1459 (2)| 00:00:07 |
    | 319 |. NESTED LOOPS | | 195 | 7215 | | 874 (2)| 00:00:04 |
    | 320 |. TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 12 | | 2 (0)| 00:00:01 |
    |*321 |. INDEX UNIQUE SCAN | I_IND1 | 1 | | | 1 (0)| 00:00:01 |
    |*322 |. VIEW | TABPARTV$ | 195 | 4875 | | 872 (2)| 00:00:04 |
    | 323 |. WINDOW SORT | | 96857 | 1513K| 2672K| 872 (2)| 00:00:04 |
    | 324 |. TABLE ACCESS FULL | TABPART$ | 96857 | 1513K| | 414 (2)| 00:00:02 |
    |*325 |. INDEX RANGE SCAN | I_OBJ1 | 1 | | | 2 (0)| 00:00:01 |
    | 326 |. TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 17 | | 3 (0)| 00:00:01 |
    | 327 |. TABLE ACCESS BY INDEX ROWID | INDPART_PARAM$ | 1 | 515 | | 0 (0)| 00:00:01 |
    |*328 |. INDEX UNIQUE SCAN | I_INDPART_PARAM | 1 | | | 0 (0)| 00:00:01 |
    | 329 |. VIEW | | 33408 | 150M| | 2265 (1)| 00:00:10 |
    | 330 |. SORT ORDER BY | | 33408 | 11M| 13M| 2265 (1)| 00:00:10 |
    |*331 |. HASH JOIN | | 33408 | 11M| | 33 (4)| 00:00:01 |
    | 332 |. TABLE ACCESS FULL | TS$ | 138 | 3036 | | 26 (0)| 00:00:01 |
    | 333 |. VIEW | INDPARTV$ | 139 | 46843 | | 6 (0)| 00:00:01 |
    | 334 |. WINDOW BUFFER | | 139 | 20572 | | 6 (0)| 00:00:01 |
    | 335 |. TABLE ACCESS BY INDEX ROWID | INDPART$ | 139 | 20572 | | 6 (0)| 00:00:01 |
    |*336 |. INDEX RANGE SCAN | I_INDPART_BOPART$ | 139 | | | 2 (0)| 00:00:01 |
    | 337 |. TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*338 |. INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 339 |. TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*340 |. INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 341 |. NESTED LOOPS | | 1 | 120 | | 5 (0)| 00:00:01 |
    | 342 |. TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 101 | | 4 (0)| 00:00:01 |
    |*343 |. INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 344 |. TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*345 |. INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 346 |. TABLE ACCESS CLUSTER | SEG$ | 1 | 70 | | 3 (0)| 00:00:01 |
    |*347 |. INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | | 2 (0)| 00:00:01 |
    | 348 |. TABLE ACCESS BY INDEX ROWID | DEFERRED_STG$ | 1 | 28 | | 2 (0)| 00:00:01 |
    |*349 |. INDEX UNIQUE SCAN | I_DEFERRED_STG1 | 1 | | | 1 (0)| 00:00:01 |
    | 350 |. NESTED LOOPS | | | | | | |
    | 351 |. NESTED LOOPS | | 2 | 396 | | 185 (11)| 00:00:01 |
    |*352 |. HASH JOIN | | 2 | 362 | | 179 (11)| 00:00:01 |
    |*353 |. VIEW | INDCOMPARTV$ | 9113 | 284K| | 52 (4)| 00:00:01 |
    | 354 |. WINDOW SORT | | 9113 | 142K| | 52 (4)| 00:00:01 |
    | 355 |. TABLE ACCESS FULL | INDCOMPART$ | 9113 | 142K| | 51 (2)| 00:00:01 |
    | 356 |. NESTED LOOPS | | 1301K| 107M| | 114 (4)| 00:00:01 |
    |*357 |. HASH JOIN | | 5 | 375 | | 109 (4)| 00:00:01 |
    | 358 |. VIEW | TABCOMPARTV$ | 12265 | 299K| | 107 (3)| 00:00:01 |
    | 359 |. WINDOW SORT | | 12265 | 191K| | 107 (3)| 00:00:01 |
    | 360 |. TABLE ACCESS FULL | TABCOMPART$ | 12265 | 191K| | 105 (1)| 00:00:01 |
    |*361 |. VIEW | TABSUBPARTV$ | 361K| 8833K| | 1 (0)| 00:00:01 |
    | 362 |. WINDOW SORT | | 361K| 5653K| 9960K| 3006 (2)| 00:00:13 |
  • 3. Re: Performance issue when trying to execute DBMS_METADATA.GET_DDL of a table
    982500 Newbie
    Currently Being Moderated
    | 363 |. TABLE ACCESS FULL | TABSUBPART$ | 361K| 5653K| | 1308 (3)| 00:00:06 |
    | 364 |. TABLE ACCESS CLUSTER | IND$ | 260K| 3049K| | 1 (0)| 00:00:01 |
    |*365 |. INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    |*366 |. INDEX RANGE SCAN | I_OBJ1 | 1 | | | 2 (0)| 00:00:01 |
    | 367 |. TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 17 | | 3 (0)| 00:00:01 |
    | 368 |. VIEW | | 179K| 371M| | 10337 (1)| 00:00:44 |
    | 369 |. SORT ORDER BY | | 179K| 52M| 56M| 10337 (1)| 00:00:44 |
    | 370 |. NESTED LOOPS | | 179K| 52M| | 26 (0)| 00:00:01 |
    | 371 |. VIEW | INDSUBPARTV$ | 22 | 6292 | | 4 (0)| 00:00:01 |
    | 372 |. WINDOW BUFFER | | 22 | 1584 | | 4 (0)| 00:00:01 |
    | 373 |. TABLE ACCESS BY INDEX ROWID | INDSUBPART$ | 22 | 1584 | | 4 (0)| 00:00:01 |
    |*374 |. INDEX RANGE SCAN | I_INDSUBPART_POBJSUBPART$ | 22 | | | 3 (0)| 00:00:01 |
    | 375 |. TABLE ACCESS CLUSTER | TS$ | 8145 | 174K| | 1 (0)| 00:00:01 |
    |*376 |. INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 377 |. TABLE ACCESS CLUSTER | TS$ | 1 | 18 | | 1 (0)| 00:00:01 |
    |*378 |. INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 379 |. TABLE ACCESS CLUSTER | TS$ | 1 | 8 | | 1 (0)| 00:00:01 |
    |*380 |. INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 381 |. VIEW | | 15343 | 55M| | 2218 (1)| 00:00:10 |
    | 382 |. SORT ORDER BY | | 15343 | 8240K| 8776K| 2218 (1)| 00:00:10 |
    |*383 |. HASH JOIN | | 15343 | 8240K| | 661 (1)| 00:00:03 |
    | 384 |. TABLE ACCESS FULL | USER$ | 184 | 3496 | | 4 (0)| 00:00:01 |
    | 385 |. NESTED LOOPS | | | | | | |
    | 386 |. NESTED LOOPS | | 15343 | 7956K| | 657 (1)| 00:00:03 |
    | 387 |. VIEW | INDCOMPARTV$ | 130 | 55900 | | 5 (0)| 00:00:01 |
    | 388 |. WINDOW BUFFER | | 130 | 9100 | | 5 (0)| 00:00:01 |
    | 389 |. TABLE ACCESS BY INDEX ROWID | INDCOMPART$ | 130 | 9100 | | 5 (0)| 00:00:01 |
    |*390 |. INDEX RANGE SCAN | I_INDCOMPART_BOPART$ | 130 | | | 2 (0)| 00:00:01 |
    |*391 |. INDEX RANGE SCAN | I_OBJ1 | 118 | | | 2 (0)| 00:00:01 |
    | 392 |. TABLE ACCESS BY INDEX ROWID | OBJ$ | 118 | 11918 | | 5 (0)| 00:00:01 |
    | 393 | OOPS NESTED L | | 1 | 61 | | 2 (0)| 00:00:01 |
    | 394 | CCESS BY INDEX ROWID TABLE A | PARTOBJ$ | 1 | 55 | | 2 (0)| 00:00:01 |
    |*395 | UNIQUE SCAN INDEX | I_PARTOBJ$ | 1 | | | 1 (0)| 00:00:01 |
    |*396 | NIQUE SCAN INDEX U | I_IND1 | 1 | 6 | | 0 (0)| 00:00:01 |
    |*397 | FILTER | | | | | | |
    |*398 | ACCESS BY INDEX ROWID TABLE | CDEF$ | 1 | 13 | | 2 (0)| 00:00:01 |
    |*399 | X RANGE SCAN INDE | I_CDEF4 | 5 | | | 1 (0)| 00:00:01 |
    |*400 | ACCESS CLUSTER TABLE | TAB$ | 1 | 13 | | 2 (0)| 00:00:01 |
    |*401 | X UNIQUE SCAN INDE | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*402 | R FILTE | | | | | | |
    | 403 | DUAL FAST | | 1 | | | 2 (0)| 00:00:01 |
    |*404 | E ACCESS BY INDEX ROWID TABL | CDEF$ | 1 | 10 | | 2 (0)| 00:00:01 |
    |*405 | EX RANGE SCAN IND | I_CDEF4 | 5 | | | 1 (0)| 00:00:01 |
    |*406 | ER FILT | | | | | | |
    | 407 | TED LOOPS NES | | 1 | 41 | | 5 (0)| 00:00:01 |
    | 408 | STED LOOPS NE | | 1 | 28 | | 4 (0)| 00:00:01 |
    | 409 | ABLE ACCESS BY INDEX ROWID T | IND$ | 1 | 10 | | 2 (0)| 00:00:01 |
    |*410 |.INDEX UNIQUE SCAN | I_IND1 | 1 | | | 1 (0)| 00:00:01 |
    | 411 | ABLE ACCESS BY INDEX ROWID T | ICOL$ | 1 | 18 | | 2 (0)| 00:00:01 |
    |*412 |.INDEX RANGE SCAN | I_ICOL1 | 1 | | | 1 (0)| 00:00:01 |
    |*413 | BLE ACCESS CLUSTER TA | COL$ | 1 | 13 | | 1 (0)| 00:00:01 |
    | 414 | NESTED LOOPS | | 1 | 235 | | 7 (0)| 00:00:01 |
    | 415 | MERGE JOIN CARTESIAN | | 1 | 216 | | 6 (0)| 00:00:01 |
    | 416 | NESTED LOOPS | | 1 | 115 | | 3 (0)| 00:00:01 |
    | 417 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 93 | | 2 (0)| 00:00:01 |
    |*418 | INDEX UNIQUE SCAN | I_IND1 | 1 | | | 1 (0)| 00:00:01 |
    | 419 | TABLE ACCESS CLUSTER | TS$ | 1 | 22 | | 1 (0)| 00:00:01 |
    |*420 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 421 | BUFFER SORT | | 1 | 101 | | 5 (0)| 00:00:01 |
    |*422 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 101 | | 3 (0)| 00:00:01 |
    |*423 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 2 (0)| 00:00:01 |
    | 424 | NESTED LOOPS | | 1 | 23 | | 1 (0)| 00:00:01 |
    |*425 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*426 | INDEX UNIQUE SCAN | I_USER1 | 1 | | | 0 (0)| 00:00:01 |
    |*427 | FIXED TABLE FULL | X$KZSRO | 1 | 4 | | 0 (0)| 00:00:01 |
    | 428 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*429 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 430 | NESTED LOOPS | | | | | | |
    | 431 | NESTED LOOPS | | 1 | 75 | | 3 (0)| 00:00:01 |
    |*432 | TABLE ACCESS CLUSTER | CDEF$ | 1 | 50 | | 2 (0)| 00:00:01 |
    |*433 | INDEX UNIQUE SCAN | I_COBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*434 | INDEX UNIQUE SCAN | I_CON2 | 1 | | | 0 (0)| 00:00:01 |
    | 435 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 25 | | 1 (0)| 00:00:01 |
    | 436 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*437 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 438 | NESTED LOOPS | | 1 | 120 | | 5 (0)| 00:00:01 |
    | 439 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 101 | | 4 (0)| 00:00:01 |
    |*440 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 441 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*442 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 443 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 47 | | 2 (0)| 00:00:01 |
    |*444 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | | 1 (0)| 00:00:01 |
    | 445 | NESTED LOOPS | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 446 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |*447 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 448 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*449 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 450 | NESTED LOOPS | | 1 | 105 | | 6 (0)| 00:00:01 |
    | 451 | NESTED LOOPS | | 1 | 85 | | 5 (0)| 00:00:01 |
    | 452 | NESTED LOOPS | | 1 | 77 | | 4 (0)| 00:00:01 |
    | 453 | NESTED LOOPS | | 1 | 56 | | 3 (0)| 00:00:01 |
    |*454 | TABLE ACCESS CLUSTER | COL$ | 1 | 39 | | 2 (0)| 00:00:01 |
    |*455 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*456 | TABLE ACCESS CLUSTER | CCOL$ | 1 | 17 | | 1 (0)| 00:00:01 |
    |*457 | INDEX UNIQUE SCAN | I_COBJ# | 1 | | | 0 (0)| 00:00:01 |
    |*458 | TABLE ACCESS CLUSTER | COLTYPE$ | 1 | 21 | | 1 (0)| 00:00:01 |
    |*459 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    |*460 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 8 | | 1 (0)| 00:00:01 |
    |*461 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | | 0 (0)| 00:00:01 |
    |*462 | TABLE ACCESS CLUSTER | COL$ | 1 | 20 | | 1 (0)| 00:00:01 |
    |*463 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    | 464 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 47 | | 2 (0)| 00:00:01 |
    |*465 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | | 1 (0)| 00:00:01 |
    | 466 | NESTED LOOPS | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 467 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |*468 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 469 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*470 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 471 | NESTED LOOPS | | 1 | 70 | | 3 (0)| 00:00:01 |
    |*472 | TABLE ACCESS CLUSTER | COL$ | 1 | 39 | | 2 (0)| 00:00:01 |
    |*473 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*474 | TABLE ACCESS CLUSTER | COL$ | 1 | 31 | | 1 (0)| 00:00:01 |
    |*475 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    | 476 | NESTED LOOPS | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 477 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |*478 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 479 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*480 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 481 | VIEW | | 1 | 102 | | 5 (20)| 00:00:01 |
    | 482 | SORT ORDER BY | | 1 | 68 | | 5 (20)| 00:00:01 |
    | 483 | NESTED LOOPS | | 1 | 68 | | 4 (0)| 00:00:01 |
    | 484 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 19 | | 3 (0)| 00:00:01 |
    |*485 | INDEX RANGE SCAN | I_CCOL1 | 1 | | | 2 (0)| 00:00:01 |
    |*486 | TABLE ACCESS CLUSTER | COL$ | 1 | 49 | | 1 (0)| 00:00:01 |
    |*487 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    | 488 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 47 | | 2 (0)| 00:00:01 |
    |*489 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | | 1 (0)| 00:00:01 |
    | 490 | NESTED LOOPS | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 491 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |*492 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 493 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*494 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 495 | NESTED LOOPS | | 1 | 105 | | 6 (0)| 00:00:01 |
    | 496 | NESTED LOOPS | | 1 | 85 | | 5 (0)| 00:00:01 |
    | 497 | NESTED LOOPS | | 1 | 77 | | 4 (0)| 00:00:01 |
    | 498 | NESTED LOOPS | | 1 | 56 | | 3 (0)| 00:00:01 |
    |*499 | TABLE ACCESS CLUSTER | COL$ | 1 | 39 | | 2 (0)| 00:00:01 |
    |*500 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*501 | TABLE ACCESS CLUSTER | CCOL$ | 1 | 17 | | 1 (0)| 00:00:01 |
    |*502 | INDEX UNIQUE SCAN | I_COBJ# | 1 | | | 0 (0)| 00:00:01 |
    |*503 | TABLE ACCESS CLUSTER | COLTYPE$ | 1 | 21 | | 1 (0)| 00:00:01 |
    |*504 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    |*505 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 8 | | 1 (0)| 00:00:01 |
    |*506 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | | 0 (0)| 00:00:01 |
    |*507 | TABLE ACCESS CLUSTER | COL$ | 1 | 20 | | 1 (0)| 00:00:01 |
    |*508 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    | 509 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 47 | | 2 (0)| 00:00:01 |
    |*510 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | | 1 (0)| 00:00:01 |
    | 511 | NESTED LOOPS | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 512 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |*513 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 514 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*515 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 516 | NESTED LOOPS | | 1 | 70 | | 3 (0)| 00:00:01 |
    |*517 | TABLE ACCESS CLUSTER | COL$ | 1 | 39 | | 2 (0)| 00:00:01 |
    |*518 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*519 | TABLE ACCESS CLUSTER | COL$ | 1 | 31 | | 1 (0)| 00:00:01 |
    |*520 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    | 521 | NESTED LOOPS | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 522 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |*523 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 524 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*525 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 526 | VIEW | | 1 | 102 | | 5 (20)| 00:00:01 |
    | 527 | SORT ORDER BY | | 1 | 68 | | 5 (20)| 00:00:01 |
    | 528 | NESTED LOOPS | | 1 | 68 | | 4 (0)| 00:00:01 |
    | 529 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 19 | | 3 (0)| 00:00:01 |
    |*530 | INDEX RANGE SCAN | I_CCOL1 | 1 | | | 2 (0)| 00:00:01 |
    |*531 | TABLE ACCESS CLUSTER | COL$ | 1 | 49 | | 1 (0)| 00:00:01 |
    |*532 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    | 533 | NESTED LOOPS | | | | | | |
    | 534 | NESTED LOOPS | | 1 | 77 | | 3 (0)| 00:00:01 |
    |*535 | TABLE ACCESS CLUSTER | CDEF$ | 1 | 52 | | 2 (0)| 00:00:01 |
    |*536 | INDEX UNIQUE SCAN | I_COBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*537 | INDEX UNIQUE SCAN | I_CON2 | 1 | | | 0 (0)| 00:00:01 |
    | 538 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 25 | | 1 (0)| 00:00:01 |
    | 539 | TABLE ACCESS CLUSTER | TS$ | 1 | 18 | | 1 (0)| 00:00:01 |
    |*540 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 541 | TABLE ACCESS CLUSTER | TS$ | 1 | 8 | | 1 (0)| 00:00:01 |
    |*542 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 543 | VIEW | | 1 | 56 | | 4 (25)| 00:00:01 |
    | 544 | SORT ORDER BY | | 1 | 57 | | 4 (25)| 00:00:01 |
    | 545 | NESTED LOOPS | | 1 | 57 | | 3 (0)| 00:00:01 |
    |*546 | TABLE ACCESS FULL | INSERT_TSN_LIST$ | 1 | 39 | | 2 (0)| 00:00:01 |
    | 547 | TABLE ACCESS CLUSTER | TS$ | 1 | 18 | | 1 (0)| 00:00:01 |
    |*548 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 549 | NESTED LOOPS | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 550 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |*551 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 552 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*553 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 554 | VIEW | | 1 | 86 | | 4 (25)| 00:00:01 |
    | 555 | SORT ORDER BY | | 1 | 64 | | 4 (25)| 00:00:01 |
    | 556 | NESTED LOOPS | | 1 | 64 | | 3 (0)| 00:00:01 |
    | 557 | TABLE ACCESS BY INDEX ROWID | PARTCOL$ | 1 | 15 | | 2 (0)| 00:00:01 |
    |*558 | INDEX RANGE SCAN | I_PARTCOL$ | 1 | | | 1 (0)| 00:00:01 |
    |*559 | TABLE ACCESS CLUSTER | COL$ | 1 | 49 | | 1 (0)| 00:00:01 |
    |*560 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    | 561 | NESTED LOOPS | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 562 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
  • 4. Re: Performance issue when trying to execute DBMS_METADATA.GET_DDL of a table
    982500 Newbie
    Currently Being Moderated
    |*563 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 564 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*565 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 566 | VIEW | | 1 | 86 | | 4 (25)| 00:00:01 |
    | 567 | SORT ORDER BY | | 1 | 64 | | 4 (25)| 00:00:01 |
    | 568 | NESTED LOOPS | | 1 | 64 | | 3 (0)| 00:00:01 |
    | 569 | ID TABLE ACCESS BY INDEX ROW | SUBPARTCOL$ | 1 | 15 | | 2 (0)| 00:00:01 |
    |*570 | INDEX RANGE SCAN | I_SUBPARTCOL$ | 1 | | | 1 (0)| 00:00:01 |
    |*571 | TABLE ACCESS CLUSTER | COL$ | 1 | 49 | | 1 (0)| 00:00:01 |
    |*572 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    | 573 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*574 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 575 | TABLE ACCESS CLUSTER | SEG$ | 1 | 70 | | 3 (0)| 00:00:01 |
    |*576 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | | 2 (0)| 00:00:01 |
    | 577 | ROWID TABLE ACCESS BY INDEX | DEFERRED_STG$ | 1 | 28 | | 2 (0)| 00:00:01 |
    |*578 | INDEX UNIQUE SCAN | I_DEFERRED_STG1 | 1 | | | 1 (0)| 00:00:01 |
    | 579 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*580 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 581 | NESTED LOOPS | | 1 | 120 | | 5 (0)| 00:00:01 |
    | 582 | X ROWID TABLE ACCESS BY INDE | OBJ$ | 1 | 101 | | 4 (0)| 00:00:01 |
    |*583 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 584 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*585 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 586 | R TABLE ACCESS CLUSTE | SEG$ | 1 | 70 | | 3 (0)| 00:00:01 |
    |*587 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | | 2 (0)| 00:00:01 |
    | 588 | DEX ROWID TABLE ACCESS BY IN | DEFERRED_STG$ | 1 | 28 | | 2 (0)| 00:00:01 |
    |*589 | INDEX UNIQUE SCAN | I_DEFERRED_STG1 | 1 | | | 1 (0)| 00:00:01 |
    | 590 | TER TABLE ACCESS CLUS | TS$ | 1 | 18 | | 1 (0)| 00:00:01 |
    |*591 | N INDEX UNIQUE SCA | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 592 | STER TABLE ACCESS CLU | TS$ | 1 | 8 | | 1 (0)| 00:00:01 |
    |*593 | AN INDEX UNIQUE SC | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 594 | USTER TABLE ACCESS CL | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*595 | CAN INDEX UNIQUE S | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 596 | LUSTER TABLE ACCESS C | SEG$ | 1 | 70 | | 3 (0)| 00:00:01 |
    |*597 | SCAN INDEX UNIQUE | I_FILE#_BLOCK# | 1 | | | 2 (0)| 00:00:01 |
    | 598 | BY INDEX ROWID TABLE ACCESS | DEFERRED_STG$ | 1 | 28 | | 2 (0)| 00:00:01 |
    |*599 | SCAN INDEX UNIQUE | I_DEFERRED_STG1 | 1 | | | 1 (0)| 00:00:01 |
    | 600 | NESTED LOOPS | | 1 | 447 | | 900 (1)| 00:00:04 |
    |*601 | HASH JOIN | | 1 | 425 | | 899 (1)| 00:00:04 |
    | 602 | NESTED LOOPS | | 1 | 120 | | 5 (0)| 00:00:01 |
    | 603 | BY INDEX ROWID TABLE ACCESS | OBJ$ | 1 | 101 | | 4 (0)| 00:00:01 |
    |*604 | SCAN INDEX RANGE | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 605 | CLUSTER TABLE ACCESS | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*606 | E SCAN INDEX UNIQU | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    |*607 | VIEW | INDPARTV$ | 33387 | 9944K| | 893 (1)| 00:00:04 |
    | 608 | R WINDOW BUFFE | | 33387 | 4336K| | 893 (1)| 00:00:04 |
    | 609 | S BY INDEX ROWID TABLE ACCES | INDPART$ | 33387 | 4336K| | 893 (1)| 00:00:04 |
    | 610 | SCAN INDEX FULL | I_INDPART_BOPART$ | 33387 | | | 103 (1)| 00:00:01 |
    | 611 | LUSTER TABLE ACCESS C | TS$ | 1 | 22 | | 1 (0)| 00:00:01 |
    |*612 | SCAN INDEX UNIQUE | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 613 | VIEW | | 19858 | 40M| | 1471 (1)| 00:00:07 |
    | 614 | SORT ORDER BY | | 19858 | 3956K| 4688K| 1471 (1)| 00:00:07 |
    |*615 | HASH JOIN | | 19858 | 3956K| | 698 (1)| 00:00:03 |
    | 616 | TABLE ACCESS FULL | LOB$ | 1547 | 24752 | | 461 (1)| 00:00:02 |
    |*617 | VIEW | LOBFRAGV$ | 19858 | 3645K| | 236 (1)| 00:00:01 |
    | 618 | WINDOW BUFFER | | 19858 | 1066K| | 236 (1)| 00:00:01 |
    | 619 | INDEX ROWID TABLE ACCESS BY | LOBFRAG$ | 19858 | 1066K| | 236 (1)| 00:00:01 |
    | 620 | INDEX FULL SCAN | I_LOBFRAG_PARENTOBJFRAG$ | 19858 | | | 61 (0)| 00:00:01 |
    | 621 | ER TABLE ACCESS CLUST | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*622 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 623 | NESTED LOOPS | | 1 | 120 | | 5 (0)| 00:00:01 |
    | 624 | NDEX ROWID TABLE ACCESS BY I | OBJ$ | 1 | 101 | | 4 (0)| 00:00:01 |
    |*625 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 626 | TER TABLE ACCESS CLUS | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*627 | N INDEX UNIQUE SCA | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 628 | NESTED LOOPS | | 1 | 53 | | 5 (0)| 00:00:01 |
    | 629 | INDEX ROWID TABLE ACCESS BY | OBJ$ | 1 | 34 | | 4 (0)| 00:00:01 |
    |*630 | AN INDEX RANGE SC | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 631 | USTER TABLE ACCESS CL | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*632 | CAN INDEX UNIQUE S | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    |*633 | STER TABLE ACCESS CLU | COL$ | 1 | 49 | | 2 (0)| 00:00:01 |
    |*634 | AN INDEX UNIQUE SC | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    | 635 | USTER TABLE ACCESS CL | TAB$ | 1 | 13 | | 2 (0)| 00:00:01 |
    |*636 | CAN INDEX UNIQUE S | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    | 637 | Y INDEX ROWID TABLE ACCESS B | COLTYPE$ | 1 | 13 | | 2 (0)| 00:00:01 |
    |*638 | SCAN INDEX UNIQUE | I_COLTYPE2 | 1 | | | 1 (0)| 00:00:01 |
    | 639 | CLUSTER TABLE ACCESS | SEG$ | 1 | 70 | | 3 (0)| 00:00:01 |
    |*640 | SCAN INDEX UNIQUE | I_FILE#_BLOCK# | 1 | | | 2 (0)| 00:00:01 |
    | 641 | BY INDEX ROWID TABLE ACCESS | DEFERRED_STG$ | 1 | 28 | | 2 (0)| 00:00:01 |
    |*642 | E SCAN INDEX UNIQU | I_DEFERRED_STG1 | 1 | | | 1 (0)| 00:00:01 |
    | 643 | S CLUSTER TABLE ACCES | TS$ | 1 | 18 | | 1 (0)| 00:00:01 |
    |*644 | UE SCAN INDEX UNIQ | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 645 | SS CLUSTER TABLE ACCE | TS$ | 1 | 8 | | 1 (0)| 00:00:01 |
    |*646 | QUE SCAN INDEX UNI | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 647 | NESTED LOOPS | | 1 | 55 | | 3 (0)| 00:00:01 |
    | 648 | BY INDEX ROWID TABLE ACCESS | TABPART$ | 1 | 42 | | 2 (0)| 00:00:01 |
    |*649 | E SCAN INDEX UNIQU | I_TABPART_OBJ$ | 1 | | | 1 (0)| 00:00:01 |
    |*650 | CLUSTER TABLE ACCESS | TAB$ | 1 | 13 | | 1 (0)| 00:00:01 |
    |*651 | E SCAN INDEX UNIQU | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    |*652 | FILTER | | | | | | |
    | 653 | MERGE JOIN | | 53239 | 2287K| | 1334 (2)| 00:00:06 |
    |*654 | VIEW | TABPARTV$ | 96857 | 3026K| | 872 (2)| 00:00:04 |
    | 655 | WINDOW SORT | | 96857 | 1513K| 2672K| 872 (2)| 00:00:04 |
    | 656 | ULL TABLE ACCESS F | TABPART$ | 96857 | 1513K| | 414 (2)| 00:00:02 |
    |*657 | SORT JOIN | | 273 | 3276 | | 462 (1)| 00:00:02 |
    | 658 | LL TABLE ACCESS FU | NTAB$ | 273 | 3276 | | 461 (1)| 00:00:02 |
    |*659 | FILTER | | | | | | |
    |*660 | FILTERING (UNIQUE) CONNECT BY WITH | | | | | | |
    | 661 | INDEX ROWID TABLE ACCESS BY | NTAB$ | 2 | 16 | | 2 (0)| 00:00:01 |
    |*662 | AN INDEX RANGE SC | I_NTAB1 | 2 | | | 1 (0)| 00:00:01 |
    | 663 | NESTED LOOPS | | 4 | 84 | | 4 (0)| 00:00:01 |
    | 664 | P CONNECT BY PUM | | | | | | |
    | 665 | LUSTER TABLE ACCESS C | NTAB$ | 2 | 16 | | 1 (0)| 00:00:01 |
    |*666 | SCAN INDEX UNIQUE | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    |*667 | HASH JOIN | | 1 | 32 | | 5 (20)| 00:00:01 |
    |*668 | TER TABLE ACCESS CLUS | TAB$ | 1 | 13 | | 2 (0)| 00:00:01 |
    |*669 | N INDEX UNIQUE SCA | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*670 | VIEW | TABPARTV$ | 214 | 4066 | | 2 (0)| 00:00:01 |
    | 671 | WINDOW BUFFER | | 214 | 2140 | | 2 (0)| 00:00:01 |
    |*672 | N INDEX RANGE SCA | I_TABPART_BOPART$ | 214 | 2140 | | 2 (0)| 00:00:01 |
    | 673 | VIEW | | 214 | 1304K| | 1555 (2)| 00:00:07 |
    | 674 | SORT ORDER BY | | 214 | 72546 | | 1555 (2)| 00:00:07 |
    |*675 | HASH JOIN | | 214 | 72546 | | 1554 (2)| 00:00:07 |
    | 676 | TABLE ACCESS FULL | USER$ | 184 | 3496 | | 4 (0)| 00:00:01 |
    | 677 | NESTED LOOPS | | | | | | |
    | 678 | NESTED LOOPS | | 214 | 68480 | | 1549 (2)| 00:00:07 |
    |*679 | HASH JOIN | | 214 | 46866 | | 907 (2)| 00:00:04 |
    |*680 | HASH JOIN | | 214 | 42800 | | 34 (3)| 00:00:01 |
    | 681 | TABLE ACCESS FULL | TS$ | 138 | 3036 | | 26 (0)| 00:00:01 |
    | 682 | NDEX ROWID TABLE ACCESS BY I | TABPART$ | 214 | 38092 | | 7 (0)| 00:00:01 |
    |*683 | INDEX RANGE SCAN | I_TABPART_BOPART$ | 214 | | | 2 (0)| 00:00:01 |
    | 684 | VIEW | TABPARTV$ | 96857 | 1797K| | 872 (2)| 00:00:04 |
    | 685 | WINDOW SORT | | 96857 | 1513K| 2672K| 872 (2)| 00:00:04 |
    | 686 | L TABLE ACCESS FUL | TABPART$ | 96857 | 1513K| | 414 (2)| 00:00:02 |
    |*687 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 2 (0)| 00:00:01 |
    | 688 | X ROWID TABLE ACCESS BY INDE | OBJ$ | 1 | 101 | | 3 (0)| 00:00:01 |
    | 689 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*690 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 691 | TABLE ACCESS CLUSTER | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*692 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 693 | R TABLE ACCESS CLUSTE | SEG$ | 1 | 70 | | 3 (0)| 00:00:01 |
    |*694 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | | 2 (0)| 00:00:01 |
    | 695 | DEX ROWID TABLE ACCESS BY IN | DEFERRED_STG$ | 1 | 28 | | 2 (0)| 00:00:01 |
    |*696 | INDEX UNIQUE SCAN | I_DEFERRED_STG1 | 1 | | | 1 (0)| 00:00:01 |
    | 697 | TER TABLE ACCESS CLUS | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*698 | N INDEX UNIQUE SCA | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 699 | NESTED LOOPS | | 1 | 120 | | 5 (0)| 00:00:01 |
    | 700 | INDEX ROWID TABLE ACCESS BY | OBJ$ | 1 | 101 | | 4 (0)| 00:00:01 |
    |*701 | N INDEX RANGE SCA | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 702 | STER TABLE ACCESS CLU | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*703 | AN INDEX UNIQUE SC | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 704 | USTER TABLE ACCESS CL | SEG$ | 1 | 70 | | 3 (0)| 00:00:01 |
    |*705 | CAN INDEX UNIQUE S | I_FILE#_BLOCK# | 1 | | | 2 (0)| 00:00:01 |
    | 706 | Y INDEX ROWID TABLE ACCESS B | DEFERRED_STG$ | 1 | 28 | | 2 (0)| 00:00:01 |
    |*707 | SCAN INDEX UNIQUE | I_DEFERRED_STG1 | 1 | | | 1 (0)| 00:00:01 |
    | 708 | CLUSTER TABLE ACCESS | TS$ | 1 | 18 | | 1 (0)| 00:00:01 |
    |*709 | SCAN INDEX UNIQUE | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 710 | CLUSTER TABLE ACCESS | TS$ | 1 | 8 | | 1 (0)| 00:00:01 |
    |*711 | E SCAN INDEX UNIQU | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 712 | S CLUSTER TABLE ACCES | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*713 | UE SCAN INDEX UNIQ | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 714 | SS CLUSTER TABLE ACCE | SEG$ | 1 | 70 | | 3 (0)| 00:00:01 |
    |*715 | QUE SCAN INDEX UNI | I_FILE#_BLOCK# | 1 | | | 2 (0)| 00:00:01 |
    | 716 | ESS BY INDEX ROWID TABLE ACC | DEFERRED_STG$ | 1 | 28 | | 2 (0)| 00:00:01 |
    |*717 | IQUE SCAN INDEX UN | I_DEFERRED_STG1 | 1 | | | 1 (0)| 00:00:01 |
    | 718 | S NESTED LOOP | | 1 | 421 | | 2864 (1)| 00:00:13 |
    | 719 | PS NESTED LOO | | 1 | 402 | | 2863 (1)| 00:00:13 |
    | 720 | N MERGE JOI | | 1 | 380 | | 2862 (1)| 00:00:13 |
    |*721 | VIEW | INDSUBPARTV$ | 203K| 54M| | 2857 (1)| 00:00:13 |
    | 722 | BUFFER WINDOW | | 203K| 13M| | 2857 (1)| 00:00:13 |
    | 723 | ACCESS BY INDEX ROWID TABLE | INDSUBPART$ | 203K| 13M| | 2857 (1)| 00:00:13 |
    | 724 | FULL SCAN INDEX | I_INDSUBPART_POBJSUBPART$ | 203K| | | 517 (1)| 00:00:03 |
    |*725 | N SORT JOI | | 1 | 101 | | 5 (20)| 00:00:01 |
    | 726 | CCESS BY INDEX ROWID TABLE A | OBJ$ | 1 | 101 | | 4 (0)| 00:00:01 |
    |*727 | RANGE SCAN INDEX | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
    | 728 | ESS CLUSTER TABLE ACC | TS$ | 1 | 22 | | 1 (0)| 00:00:01 |
    |*729 | IQUE SCAN INDEX UN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 730 | SS CLUSTER TABLE ACCE | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*731 | QUE SCAN INDEX UNI | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 732 | VIEW | | 3220 | 6653K| | 252 (2)| 00:00:02 |
    | 733 | SORT ORDER BY | | 3220 | 679K| | 252 (2)| 00:00:02 |
    |*734 | HASH JOIN | | 3220 | 679K| | 251 (1)| 00:00:02 |
    | 735 | NESTED LOOPS | | | | | | |
    | 736 | NESTED LOOPS | | 12 | 336 | | 14 (0)| 00:00:01 |
    | 737 | FULL TABLE ACCESS | LOBCOMPPART$ | 12 | 144 | | 2 (0)| 00:00:01 |
    |*738 | SCAN INDEX UNIQUE | I_LOB2 | 1 | | | 0 (0)| 00:00:01 |
    | 739 | BY INDEX ROWID TABLE ACCESS | LOB$ | 1 | 16 | | 1 (0)| 00:00:01 |
    |*740 | VIEW | LOBFRAGV$ | 19858 | 3645K| | 236 (1)| 00:00:01 |
    | 741 | WINDOW BUFFER | | 19858 | 1066K| | 236 (1)| 00:00:01 |
    | 742 | BY INDEX ROWID TABLE ACCESS | LOBFRAG$ | 19858 | 1066K| | 236 (1)| 00:00:01 |
    | 743 | SCAN INDEX FULL | I_LOBFRAG_PARENTOBJFRAG$ | 19858 | | | 61 (0)| 00:00:01 |
    | 744 | Y INDEX ROWID TABLE ACCESS B | TABSUBPART$ | 1 | 111 | | 3 (0)| 00:00:01 |
    |*745 | SCAN INDEX UNIQUE | I_TABSUBPART$_OBJ$ | 1 | | | 2 (0)| 00:00:01 |
    | 746 | VIEW | | 418K| 2477M| | 38130 (1)| 00:02:41 |
    | 747 | SORT ORDER BY | | 418K| 177M| 192M| 38130 (1)| 00:02:41 |
    |*748 | HASH JOIN | | 418K| 177M| | 3590 (3)| 00:00:16 |
    | 749 | TABLE ACCESS FULL | USER$ | 184 | 3496 | | 4 (0)| 00:00:01 |
    |*750 | HASH JOIN | | 418K| 170M| | 3582 (2)| 00:00:16 |
    | 751 | L TABLE ACCESS FUL | TS$ | 138 | 3036 | | 26 (0)| 00:00:01 |
    |*752 | HASH JOIN | | 418K| 161M| | 3552 (2)| 00:00:15 |
    | 753 | VIEW | TABSUBPARTV$ | 30 | 9120 | | 4 (0)| 00:00:01 |
    | 754 | WINDOW BUFFER | | 30 | 3750 | | 4 (0)| 00:00:01 |
    | 755 | BY INDEX ROWID TABLE ACCESS | TABSUBPART$ | 30 | 3750 | | 4 (0)| 00:00:01 |
    |*756 | SCAN INDEX RANGE | I_TABSUBPART_POBJSUBPART$ | 30 | | | 3 (0)| 00:00:01 |
    | 757 | LL TABLE ACCESS FU | OBJ$ | 825K| 79M| | 3539 (2)| 00:00:15 |
    | 758 | NDEX ROWID TABLE ACCESS BY I | LOB$ | 1 | 10 | | 2 (0)| 00:00:01 |
    |*759 | N INDEX UNIQUE SCA | I_LOB2 | 1 | | | 1 (0)| 00:00:01 |
    | 760 | STER TABLE ACCESS CLU | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*761 | AN INDEX UNIQUE SC | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 762 | NESTED LOOPS | | 1 | 120 | | 5 (0)| 00:00:01 |
    | 763 | INDEX ROWID TABLE ACCESS BY | OBJ$ | 1 | 101 | | 4 (0)| 00:00:01 |
    |*764 | AN INDEX RANGE SC | I_OBJ1 | 1 | | | 3 (0)| 00:00:01 |
  • 5. Re: Performance issue when trying to execute DBMS_METADATA.GET_DDL of a table
    982500 Newbie
    Currently Being Moderated
    | 765 | USTER TABLE ACCESS CL | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |*766 | CAN INDEX UNIQUE S | I_USER# | 1 | | | 0 (0)| 00:00:01 |
    | 767 | LUSTER TABLE ACCESS C | TS$ | 1 | 18 | | 1 (0)| 00:00:01 |
    |*768 | SCAN INDEX UNIQUE | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 769 | CLUSTER TABLE ACCESS | TS$ | 1 | 8 | | 1 (0)| 00:00:01 |
    |*770 | SCAN INDEX UNIQUE | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 771 | VIEW | | 12 | 19896 | | 3 (34)| 00:00:01 |
    | 772 | SORT ORDER BY | | 12 | 3588 | | 3 (34)| 00:00:01 |
    |*773 | VIEW | LOBCOMPPARTV$ | 12 | 3588 | | 2 (0)| 00:00:01 |
    | 774 | WINDOW BUFFER | | 12 | 528 | | 2 (0)| 00:00:01 |
    | 775 | BY INDEX ROWID TABLE ACCESS | LOBCOMPPART$ | 12 | 528 | | 2 (0)| 00:00:01 |
    | 776 | CAN INDEX FULL S | I_LOBCOMPPART_LOBJPART$ | 12 | | | 1 (0)| 00:00:01 |
    | 777 | Y INDEX ROWID TABLE ACCESS B | TABCOMPART$ | 1 | 156 | | 2 (0)| 00:00:01 |
    |*778 | SCAN INDEX UNIQUE | I_TABCOMPART$ | 1 | | | 1 (0)| 00:00:01 |
    | 779 | VIEW | | 7938 | 44M| | 1334 (1)| 00:00:06 |
    | 780 | SORT ORDER BY | | 7938 | 4364K| 4544K| 1334 (1)| 00:00:06 |
    |*781 | HASH JOIN | | 7938 | 4364K| | 506 (1)| 00:00:03 |
    | 782 | TABLE ACCESS FULL | USER$ | 184 | 3496 | | 4 (0)| 00:00:01 |
    |*783 | HASH JOIN | | 7938 | 4217K| | 502 (1)| 00:00:03 |
    | 784 | TABLE ACCESS FULL | TS$ | 138 | 3036 | | 26 (0)| 00:00:01 |
    | 785 | NESTED LOOPS | | | | | | |
    | 786 | NESTED LOOPS | | 7938 | 4046K| | 475 (1)| 00:00:03 |
    | 787 | VIEW | TABCOMPARTV$ | 117 | 49257 | | 6 (0)| 00:00:01 |
    | 788 | WINDOW BUFFER | | 117 | 21060 | | 6 (0)| 00:00:01 |
    | 789 | BY INDEX ROWID TABLE ACCESS | TABCOMPART$ | 117 | 21060 | | 6 (0)| 00:00:01 |
    |*790 | SCAN INDEX RANGE | I_TABCOMPART_BOPART$ | 117 | | | 2 (0)| 00:00:01 |
    |*791 | N INDEX RANGE SCA | I_OBJ1 | 68 | | | 2 (0)| 00:00:01 |
    | 792 | INDEX ROWID TABLE ACCESS BY | OBJ$ | 68 | 6868 | | 4 (0)| 00:00:01 |
    | 793 | ER TABLE ACCESS CLUST | TS$ | 1 | 18 | | 1 (0)| 00:00:01 |
    |*794 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 795 | TER TABLE ACCESS CLUS | TS$ | 1 | 18 | | 1 (0)| 00:00:01 |
    |*796 | N INDEX UNIQUE SCA | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    | 797 | VIEW | | 1 | 119 | | 0 (0)| 00:00:01 |
    | 798 | NESTED LOOPS | | 1 | 104 | | 0 (0)| 00:00:01 |
    | 799 | INDEX ROWID TABLE ACCESS BY | DEFSUBPARTLOB$ | 1 | 84 | | 0 (0)| 00:00:01 |
    |*800 | AN INDEX RANGE SC | I_DEFSUBPARTLOB$ | 1 | | | 0 (0)| 00:00:01 |
    |*801 | USTER TABLE ACCESS CL | COL$ | 1 | 20 | | 0 (0)| 00:00:01 |
    |*802 | CAN INDEX UNIQUE S | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    | 803 | VIEW | | 47 | 189K| | 3 (0)| 00:00:01 |
    | 804 | NDEX ROWID TABLE ACCESS BY I | DEFSUBPART$ | 47 | 1645 | | 3 (0)| 00:00:01 |
    |*805 | INDEX RANGE SCAN | I_DEFSUBPART$ | 47 | | | 2 (0)| 00:00:01 |
    | 806 | TABLE ACCESS BY INDEX ROWID | PARTOBJ$ | 1 | 55 | | 2 (0)| 00:00:01 |
    |*807 | INDEX UNIQUE SCAN | I_PARTOBJ$ | 1 | | | 1 (0)| 00:00:01 |
    | 808 | VIEW | | 1 | 2045 | | 0 (0)| 00:00:01 |
    | 809 | TABLE ACCESS BY INDEX ROWID | EXTERNAL_LOCATION$ | 1 | 2045 | | 0 (0)| 00:00:01 |
    |*810 | INDEX RANGE SCAN | I_EXTERNAL_LOCATION1$ | 1 | | | 0 (0)| 00:00:01 |
    | 811 | TABLE ACCESS BY INDEX ROWID | EXTERNAL_TAB$ | 1 | 2088 | | 0 (0)| 00:00:01 |
    |*812 | INDEX UNIQUE SCAN | I_EXTERNAL_TAB1$ | 1 | | | 0 (0)| 00:00:01 |
    |*813 | TABLE ACCESS CLUSTER | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |*814 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    | 815 | VIEW | KU$_CUBE_FACT_VIEW | 1 | 130 | | 17 (18)| 00:00:01 |
    | 816 | SORT ORDER BY | | 1 | 238 | | 17 (18)| 00:00:01 |
    |*817 | HASH JOIN | | 1 | 238 | | 16 (13)| 00:00:01 |
    | 818 | NESTED LOOPS | | | | | | |
    | 819 | NESTED LOOPS | | 1 | 226 | | 1 (0)| 00:00:01 |
    | 820 | NESTED LOOPS | | 1 | 182 | | 0 (0)| 00:00:01 |
    | 821 | TABLE ACCESS BY INDEX ROWID | OLAP_TAB$ | 1 | 26 | | 0 (0)| 00:00:01 |
    |*822 | INDEX UNIQUE SCAN | I_OLAP_TAB$ | 1 | | | 0 (0)| 00:00:01 |
    |*823 | TABLE ACCESS BY INDEX ROWID | OLAP_TAB_COL$ | 1 | 156 | | 0 (0)| 00:00:01 |
    |*824 | INDEX RANGE SCAN | I_OLAP_TAB_COL$ | 1 | | | 0 (0)| 00:00:01 |
    |*825 | INDEX RANGE SCAN | I_AW_OBJ$ | 1 | | | 1 (0)| 00:00:01 |
    | 826 | TABLE ACCESS BY INDEX ROWID | AW_OBJ$ | 1 | 44 | | 1 (0)| 00:00:01 |
    | 827 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    | 828 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    | 829 | TABLE ACCESS FULL | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    |*830 | TABLE ACCESS CLUSTER | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |*831 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*832 | TABLE ACCESS CLUSTER | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |*833 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*834 | HASH JOIN | | 5 | 280 | | 18 (12)| 00:00:01 |
    | 835 | TABLE ACCESS BY INDEX ROWID | AW_OBJ$ | 1 | 44 | | 3 (0)| 00:00:01 |
    |*836 | INDEX RANGE SCAN | I_AW_OBJ$ | 1 | | | 2 (0)| 00:00:01 |
    | 837 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    | 838 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    | 839 | TABLE ACCESS FULL | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    | 840 | VIEW | KU$_CUBE_FACT_VIEW | 1 | 372 | | 17 (18)| 00:00:01 |
    | 841 | SORT ORDER BY | | 1 | 238 | | 17 (18)| 00:00:01 |
    |*842 | HASH JOIN | | 1 | 238 | | 16 (13)| 00:00:01 |
    | 843 | NESTED LOOPS | | | | | | |
    | 844 | NESTED LOOPS | | 1 | 226 | | 1 (0)| 00:00:01 |
    | 845 | NESTED LOOPS | | 1 | 182 | | 0 (0)| 00:00:01 |
    | 846 | D TABLE ACCESS BY INDEX ROWI | OLAP_TAB$ | 1 | 26 | | 0 (0)| 00:00:01 |
    |*847 | INDEX UNIQUE SCAN | I_OLAP_TAB$ | 1 | | | 0 (0)| 00:00:01 |
    |*848 | D TABLE ACCESS BY INDEX ROWI | OLAP_TAB_COL$ | 1 | 156 | | 0 (0)| 00:00:01 |
    |*849 | INDEX RANGE SCAN | I_OLAP_TAB_COL$ | 1 | | | 0 (0)| 00:00:01 |
    |*850 | INDEX RANGE SCAN | I_AW_OBJ$ | 1 | | | 1 (0)| 00:00:01 |
    | 851 | TABLE ACCESS BY INDEX ROWID | AW_OBJ$ | 1 | 44 | | 1 (0)| 00:00:01 |
    | 852 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    | 853 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    | 854 | TABLE ACCESS FULL | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    |*855 | TABLE ACCESS CLUSTER | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |*856 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*857 | TABLE ACCESS CLUSTER | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |*858 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*859 | HASH JOIN | | 5 | 280 | | 18 (12)| 00:00:01 |
    | 860 | D TABLE ACCESS BY INDEX ROWI | AW_OBJ$ | 1 | 44 | | 3 (0)| 00:00:01 |
    |*861 | INDEX RANGE SCAN | I_AW_OBJ$ | 1 | | | 2 (0)| 00:00:01 |
    | 862 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    | 863 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    | 864 | TABLE ACCESS FULL | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    | 865 | VIEW | KU$_CUBE_FACT_VIEW | 1 | 372 | | 17 (18)| 00:00:01 |
    | 866 | SORT ORDER BY | | 1 | 238 | | 17 (18)| 00:00:01 |
    |*867 | HASH JOIN | | 1 | 238 | | 16 (13)| 00:00:01 |
    | 868 | NESTED LOOPS | | | | | | |
    | 869 | NESTED LOOPS | | 1 | 226 | | 1 (0)| 00:00:01 |
    | 870 | NESTED LOOPS | | 1 | 182 | | 0 (0)| 00:00:01 |
    | 871 | OWID TABLE ACCESS BY INDEX R | OLAP_TAB$ | 1 | 26 | | 0 (0)| 00:00:01 |
    |*872 | INDEX UNIQUE SCAN | I_OLAP_TAB$ | 1 | | | 0 (0)| 00:00:01 |
    |*873 | OWID TABLE ACCESS BY INDEX R | OLAP_TAB_COL$ | 1 | 156 | | 0 (0)| 00:00:01 |
    |*874 | INDEX RANGE SCAN | I_OLAP_TAB_COL$ | 1 | | | 0 (0)| 00:00:01 |
    |*875 | INDEX RANGE SCAN | I_AW_OBJ$ | 1 | | | 1 (0)| 00:00:01 |
    | 876 | ID TABLE ACCESS BY INDEX ROW | AW_OBJ$ | 1 | 44 | | 1 (0)| 00:00:01 |
    | 877 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    | 878 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    | 879 | TABLE ACCESS FULL | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    |*880 | TABLE ACCESS CLUSTER | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |*881 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*882 | TABLE ACCESS CLUSTER | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |*883 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*884 | HASH JOIN | | 5 | 280 | | 18 (12)| 00:00:01 |
    | 885 | OWID TABLE ACCESS BY INDEX R | AW_OBJ$ | 1 | 44 | | 3 (0)| 00:00:01 |
    |*886 | INDEX RANGE SCAN | I_AW_OBJ$ | 1 | | | 2 (0)| 00:00:01 |
    | 887 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    | 888 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    | 889 | TABLE ACCESS FULL | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    | 890 | VIEW | KU$_CUBE_FACT_VIEW | 1 | 372 | | 17 (18)| 00:00:01 |
    | 891 | SORT ORDER BY | | 1 | 238 | | 17 (18)| 00:00:01 |
    |*892 | HASH JOIN | | 1 | 238 | | 16 (13)| 00:00:01 |
    | 893 | NESTED LOOPS | | | | | | |
    | 894 | NESTED LOOPS | | 1 | 226 | | 1 (0)| 00:00:01 |
    | 895 | NESTED LOOPS | | 1 | 182 | | 0 (0)| 00:00:01 |
    | 896 | X ROWID TABLE ACCESS BY INDE | OLAP_TAB$ | 1 | 26 | | 0 (0)| 00:00:01 |
    |*897 | INDEX UNIQUE SCAN | I_OLAP_TAB$ | 1 | | | 0 (0)| 00:00:01 |
    |*898 | X ROWID TABLE ACCESS BY INDE | OLAP_TAB_COL$ | 1 | 156 | | 0 (0)| 00:00:01 |
    |*899 | INDEX RANGE SCAN | I_OLAP_TAB_COL$ | 1 | | | 0 (0)| 00:00:01 |
    |*900 | INDEX RANGE SCAN | I_AW_OBJ$ | 1 | | | 1 (0)| 00:00:01 |
    | 901 | ROWID TABLE ACCESS BY INDEX | AW_OBJ$ | 1 | 44 | | 1 (0)| 00:00:01 |
    | 902 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    | 903 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    | 904 | TABLE ACCESS FULL | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    |*905 | TABLE ACCESS CLUSTER | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |*906 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*907 | TABLE ACCESS CLUSTER | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |*908 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*909 | HASH JOIN | | 5 | 280 | | 18 (12)| 00:00:01 |
    | 910 | X ROWID TABLE ACCESS BY INDE | AW_OBJ$ | 1 | 44 | | 3 (0)| 00:00:01 |
    |*911 | INDEX RANGE SCAN | I_AW_OBJ$ | 1 | | | 2 (0)| 00:00:01 |
    | 912 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    | 913 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    | 914 | TABLE ACCESS FULL | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    | 915 | VIEW | KU$_CUBE_FACT_VIEW | 1 | 372 | | 17 (18)| 00:00:01 |
    | 916 | SORT ORDER BY | | 1 | 238 | | 17 (18)| 00:00:01 |
    |*917 | HASH JOIN | | 1 | 238 | | 16 (13)| 00:00:01 |
    | 918 | NESTED LOOPS | | | | | | |
    | 919 | NESTED LOOPS | | 1 | 226 | | 1 (0)| 00:00:01 |
    | 920 | NESTED LOOPS | | 1 | 182 | | 0 (0)| 00:00:01 |
    | 921 | NDEX ROWID TABLE ACCESS BY I | OLAP_TAB$ | 1 | 26 | | 0 (0)| 00:00:01 |
    |*922 | N INDEX UNIQUE SCA | I_OLAP_TAB$ | 1 | | | 0 (0)| 00:00:01 |
    |*923 | NDEX ROWID TABLE ACCESS BY I | OLAP_TAB_COL$ | 1 | 156 | | 0 (0)| 00:00:01 |
    |*924 | INDEX RANGE SCAN | I_OLAP_TAB_COL$ | 1 | | | 0 (0)| 00:00:01 |
    |*925 | INDEX RANGE SCAN | I_AW_OBJ$ | 1 | | | 1 (0)| 00:00:01 |
    | 926 | EX ROWID TABLE ACCESS BY IND | AW_OBJ$ | 1 | 44 | | 1 (0)| 00:00:01 |
    | 927 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    | 928 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    | 929 | TABLE ACCESS FULL | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    |*930 | HASH JOIN | | 5 | 280 | | 18 (12)| 00:00:01 |
    | 931 | EX ROWID TABLE ACCESS BY IND | AW_OBJ$ | 1 | 44 | | 3 (0)| 00:00:01 |
    |*932 | INDEX RANGE SCAN | I_AW_OBJ$ | 1 | | | 2 (0)| 00:00:01 |
    | 933 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    | 934 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    | 935 | TABLE ACCESS FULL | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    |*936 | R TABLE ACCESS CLUSTE | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |*937 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*938 | ER TABLE ACCESS CLUST | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |*939 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*940 | HASH JOIN | | 5 | 280 | | 18 (12)| 00:00:01 |
    | 941 | INDEX ROWID TABLE ACCESS BY | AW_OBJ$ | 1 | 44 | | 3 (0)| 00:00:01 |
    |*942 | N INDEX RANGE SCA | I_AW_OBJ$ | 1 | | | 2 (0)| 00:00:01 |
    | 943 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    | 944 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    | 945 | ULL TABLE ACCESS F | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    | 946 | VIEW | | 1 | 374 | | 19 (16)| 00:00:01 |
    | 947 | SORT ORDER BY | | 1 | 264 | | 19 (16)| 00:00:01 |
    |*948 | FILTER | | | | | | |
    |*949 | HASH JOIN | | 1 | 264 | | 16 (13)| 00:00:01 |
    | 950 | NESTED LOOPS | | | | | | |
    | 951 | NESTED LOOPS | | 1 | 252 | | 1 (0)| 00:00:01 |
    | 952 | NESTED LOOPS | | 1 | 208 | | 0 (0)| 00:00:01 |
    | 953 | NESTED LOOPS | | 1 | 143 | | 0 (0)| 00:00:01 |
    | 954 | S BY INDEX ROWID TABLE ACCES | OLAP_TAB$ | 1 | 26 | | 0 (0)| 00:00:01 |
    |*955 | UE SCAN INDEX UNIQ | I_OLAP_TAB$ | 1 | | | 0 (0)| 00:00:01 |
    |*956 | S BY INDEX ROWID TABLE ACCES | OLAP_TAB_COL$ | 1 | 117 | | 0 (0)| 00:00:01 |
    |*957 | E SCAN INDEX RANG | I_OLAP_TAB_COL$ | 1 | | | 0 (0)| 00:00:01 |
    |*958 | BY INDEX ROWID TABLE ACCESS | OLAP_TAB_HIER$ | 1 | 65 | | 0 (0)| 00:00:01 |
    |*959 | SCAN INDEX RANGE | I_OLAP_TAB_HIER$ | 1 | | | 0 (0)| 00:00:01 |
    |*960 | CAN INDEX RANGE S | I_AW_OBJ$ | 1 | | | 1 (0)| 00:00:01 |
  • 6. Re: Performance issue when trying to execute DBMS_METADATA.GET_DDL of a table
    982500 Newbie
    Currently Being Moderated
    | 961 | Y INDEX ROWID TABLE ACCESS B | AW_OBJ$ | 1 | 44 | | 1 (0)| 00:00:01 |
    | 962 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    | 963 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    | 964 | FULL TABLE ACCESS | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    |*965 | STER TABLE ACCESS CLU | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |*966 | AN INDEX UNIQUE SC | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |*967 | STER TABLE ACCESS CLU | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |*968 | AN INDEX UNIQUE SC | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    | 969 | VIEW | KU$_CUBE_FACT_VIEW | 1 | 351 | | 17 (18)| 00:00:01 |
    | 970 | SORT ORDER BY | | 1 | 238 | | 17 (18)| 00:00:01 |
    |*971 | HASH JOIN | | 1 | 238 | | 16 (13)| 00:00:01 |
    | 972 | NESTED LOOPS | | | | | | |
    | 973 | NESTED LOOPS | | 1 | 226 | | 1 (0)| 00:00:01 |
    | 974 | NESTED LOOPS | | 1 | 182 | | 0 (0)| 00:00:01 |
    | 975 | Y INDEX ROWID TABLE ACCESS B | OLAP_TAB$ | 1 | 26 | | 0 (0)| 00:00:01 |
    |*976 | SCAN INDEX UNIQUE | I_OLAP_TAB$ | 1 | | | 0 (0)| 00:00:01 |
    |*977 | Y INDEX ROWID TABLE ACCESS B | OLAP_TAB_COL$ | 1 | 156 | | 0 (0)| 00:00:01 |
    |*978 | CAN INDEX RANGE S | I_OLAP_TAB_COL$ | 1 | | | 0 (0)| 00:00:01 |
    |*979 | N INDEX RANGE SCA | I_AW_OBJ$ | 1 | | | 1 (0)| 00:00:01 |
    | 980 | INDEX ROWID TABLE ACCESS BY | AW_OBJ$ | 1 | 44 | | 1 (0)| 00:00:01 |
    | 981 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    | 982 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    | 983 | LL TABLE ACCESS FU | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    | 984 | VIEW | KU$_CUBE_FACT_VIEW | 1 | 186 | | 17 (18)| 00:00:01 |
    | 985 | SORT ORDER BY | | 1 | 238 | | 17 (18)| 00:00:01 |
    |*986 | HASH JOIN | | 1 | 238 | | 16 (13)| 00:00:01 |
    | 987 | NESTED LOOPS | | | | | | |
    | 988 | NESTED LOOPS | | 1 | 226 | | 1 (0)| 00:00:01 |
    | 989 | NESTED LOOPS | | 1 | 182 | | 0 (0)| 00:00:01 |
    | 990 | TABLE ACCESS BY INDEX ROWID | OLAP_TAB$ | 1 | 26 | | 0 (0)| 00:00:01 |
    |*991 | INDEX UNIQUE SCAN | I_OLAP_TAB$ | 1 | | | 0 (0)| 00:00:01 |
    |*992 | TABLE ACCESS BY INDEX ROWID | OLAP_TAB_COL$ | 1 | 156 | | 0 (0)| 00:00:01 |
    |*993 | INDEX RANGE SCAN | I_OLAP_TAB_COL$ | 1 | | | 0 (0)| 00:00:01 |
    |*994 | INDEX RANGE SCAN | I_AW_OBJ$ | 1 | | | 1 (0)| 00:00:01 |
    | 995 | TABLE ACCESS BY INDEX ROWID | AW_OBJ$ | 1 | 44 | | 1 (0)| 00:00:01 |
    | 996 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    | 997 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    | 998 | TABLE ACCESS FULL | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    |*999 | TABLE ACCESS CLUSTER | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |1000 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |1001 | TABLE ACCESS CLUSTER | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |1002 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |1003 | FILTER | | | | | | |
    |1004 | TABLE ACCESS CLUSTER | COL$ | 16 | 320 | | 2 (0)| 00:00:01 |
    |1005 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |1006 | TABLE ACCESS BY INDEX ROWID | OLAP_TAB_COL$ | 1 | 52 | | 1 (0)| 00:00:01 |
    |1007 | INDEX RANGE SCAN | I_OLAP_TAB_COL$ | 1 | | | 1 (0)| 00:00:01 |
    |1008 | HASH JOIN | | 5 | 280 | | 18 (12)| 00:00:01 |
    |1009 | TABLE ACCESS BY INDEX ROWID | AW_OBJ$ | 1 | 44 | | 3 (0)| 00:00:01 |
    |1010 | INDEX RANGE SCAN | I_AW_OBJ$ | 1 | | | 2 (0)| 00:00:01 |
    |1011 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    |1012 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    |1013 | TABLE ACCESS FULL | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    |1014 | VIEW | KU$_CUBE_FACT_VIEW | 1 | 385 | | 17 (18)| 00:00:01 |
    |1015 | SORT ORDER BY | | 1 | 238 | | 17 (18)| 00:00:01 |
    |1016 | HASH JOIN | | 1 | 238 | | 16 (13)| 00:00:01 |
    |1017 | NESTED LOOPS | | | | | | |
    |1018 | NESTED LOOPS | | 1 | 226 | | 1 (0)| 00:00:01 |
    |1019 | NESTED LOOPS | | 1 | 182 | | 0 (0)| 00:00:01 |
    |1020 | TABLE ACCESS BY INDEX ROWID | OLAP_TAB$ | 1 | 26 | | 0 (0)| 00:00:01 |
    |1021 | INDEX UNIQUE SCAN | I_OLAP_TAB$ | 1 | | | 0 (0)| 00:00:01 |
    |1022 | TABLE ACCESS BY INDEX ROWID | OLAP_TAB_COL$ | 1 | 156 | | 0 (0)| 00:00:01 |
    |1023 | INDEX RANGE SCAN | I_OLAP_TAB_COL$ | 1 | | | 0 (0)| 00:00:01 |
    |1024 | INDEX RANGE SCAN | I_AW_OBJ$ | 1 | | | 1 (0)| 00:00:01 |
    |1025 | TABLE ACCESS BY INDEX ROWID | AW_OBJ$ | 1 | 44 | | 1 (0)| 00:00:01 |
    |1026 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    |1027 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    |1028 | TABLE ACCESS FULL | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    |1029 | TABLE ACCESS CLUSTER | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |1030 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |1031 | TABLE ACCESS CLUSTER | COL$ | 1 | 20 | | 2 (0)| 00:00:01 |
    |1032 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
    |1033 | HASH JOIN | | 5 | 280 | | 18 (12)| 00:00:01 |
    |1034 | TABLE ACCESS BY INDEX ROWID | AW_OBJ$ | 1 | 44 | | 3 (0)| 00:00:01 |
    |1035 | INDEX RANGE SCAN | I_AW_OBJ$ | 1 | | | 2 (0)| 00:00:01 |
    |1036 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    |1037 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    |1038 | TABLE ACCESS FULL | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    |1039 | VIEW | | 1 | 374 | | 17 (18)| 00:00:01 |
    |1040 | SORT ORDER BY | | 1 | 225 | | 17 (18)| 00:00:01 |
    |1041 | HASH JOIN | | 1 | 225 | | 16 (13)| 00:00:01 |
    |1042 | NESTED LOOPS | | | | | | |
    |1043 | NESTED LOOPS | | 1 | 213 | | 1 (0)| 00:00:01 |
    |1044 | NESTED LOOPS | | 1 | 169 | | 0 (0)| 00:00:01 |
    |1045 | WID TABLE ACCESS BY INDEX RO | OLAP_TAB$ | 1 | 26 | | 0 (0)| 00:00:01 |
    |1046 | INDEX UNIQUE SCAN | I_OLAP_TAB$ | 1 | | | 0 (0)| 00:00:01 |
    |1047 | WID TABLE ACCESS BY INDEX RO | OLAP_TAB_COL$ | 1 | 143 | | 0 (0)| 00:00:01 |
    |1048 | INDEX RANGE SCAN | I_OLAP_TAB_COL$ | 1 | | | 0 (0)| 00:00:01 |
    |1049 | INDEX RANGE SCAN | I_AW_OBJ$ | 1 | | | 1 (0)| 00:00:01 |
    |1050 | D TABLE ACCESS BY INDEX ROWI | AW_OBJ$ | 1 | 44 | | 1 (0)| 00:00:01 |
    |1051 | VIEW | | 1261 | 15132 | | 14 (8)| 00:00:01 |
    |1052 | SORT GROUP BY | | 1261 | 36569 | | 14 (8)| 00:00:01 |
    |1053 | TABLE ACCESS FULL | AW_OBJ$ | 1261 | 36569 | | 13 (0)| 00:00:01 |
    |1054 | NESTED LOOPS | | 1 | 52 | | 3 (0)| 00:00:01 |
    |1055 | TABLE ACCESS BY INDEX ROWID | OLAP_TAB$ | 1 | 39 | | 0 (0)| 00:00:01 |
    |1056 | INDEX UNIQUE SCAN | I_OLAP_TAB$ | 1 | | | 0 (0)| 00:00:01 |
    |1057 | TABLE ACCESS FULL | AW$ | 1 | 13 | | 3 (0)| 00:00:01 |
    |1058 | NESTED LOOPS | | 1 | 287 | | 6 (0)| 00:00:01 |
    |1059 | NESTED LOOPS | | 1 | 265 | | 5 (0)| 00:00:01 |
    |1060 | NESTED LOOPS | | 80 | 9600 | | 4 (0)| 00:00:01 |
    |1061 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |1062 | INDEX UNIQUE SCAN | I_USER1 | 1 | | | 0 (0)| 00:00:01 |
    |1063 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 80 | 8080 | | 4 (0)| 00:00:01 |
    |1064 | INDEX RANGE SCAN | I_OBJ2 | 1 | | | 3 (0)| 00:00:01 |
    |1065 | NESTED LOOPS | | 1 | 23 | | 1 (0)| 00:00:01 |
    |1066 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 19 | | 1 (0)| 00:00:01 |
    |1067 | INDEX UNIQUE SCAN | I_USER1 | 1 | | | 0 (0)| 00:00:01 |
    |1068 | FIXED TABLE FULL | X$KZSRO | 1 | 4 | | 0 (0)| 00:00:01 |
    |1069 | TABLE ACCESS CLUSTER | TAB$ | 1 | 145 | | 1 (0)| 00:00:01 |
    |1070 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
    |1071 | TABLE ACCESS BY INDEX ROWID | PARTOBJ$ | 1 | 9 | | 2 (0)| 00:00:01 |
    |1072 | INDEX UNIQUE SCAN | I_PARTOBJ$ | 1 | | | 1 (0)| 00:00:01 |
    |1073 | TABLE ACCESS CLUSTER | TS$ | 1 | 22 | | 1 (0)| 00:00:01 |
    |1074 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------------------------------------------------
  • 7. Re: Performance issue when trying to execute DBMS_METADATA.GET_DDL of a table
    TSharma-Oracle Guru
    Currently Being Moderated
    This is a known problem.
    The optimizer is not pushing the predicate (icp.obj# = o.obj_num) to get the minimum rows at INDCOMPART$ level is the cause of the slowness. Hence, this table is read in a full scan, and at customer end it has several millions of rows (lots of schemas each with lot of composite partitioned tables and indexes).

    This is a bug 13844935. You should get a patch from oracle Metalink.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points