Huge Execution Plan Cost Change For Fixed Tables

Prev: Please DON'T Vote for AMIN!! EVER!!!!
Next: MWA question

From: saurangshu on 25 Jun 2008 13:53

---

Hi,

We are using certain sql to get the column information for a group of
tables (in Oracle 10gR2 database). The sql is based on the underline
Oracle data dictionary tables/views and it is taking a huge amount of
time to execute only after the recent restoration of our development
database.

The sql being used is -
-----------------------------------------------------------------------------------
select
c.TABLE_NAME TABLE_NAME,
c.COLUMN_NAME COL_NAME,
c.DATA_TYPE DT_DRIVER,
substr(c.COLUMN_NAME,1,35) COL_HEADING,
c.COLUMN_ID POS,
case
when C.DATA_TYPE = 'NUMBER' then c.DATA_PRECISION
when C.DATA_TYPE = 'CHAR' then c.CHAR_LENGTH
when C.DATA_TYPE = 'NCHAR' then c.CHAR_LENGTH
when C.DATA_TYPE = 'VARCHAR2' then c.CHAR_LENGTH
when C.DATA_TYPE = 'NVARCHAR2' then c.CHAR_LENGTH
else nvl(c.DATA_PRECISION,c.DATA_LENGTH)
end LONGC,
c.DATA_SCALE SCALEC,
decode(c.NULLABLE,'Y','0','1') COL_MANDATORY
from ALL_TAB_COLUMNS c,
ALL_OBJECTS o
Where o.OWNER = 'ABC'
and (
(
o.OBJECT_NAME like 'XYZ' -- a table name
and o.OBJECT_TYPE = 'TABLE'
and instr(';'||'SY;T;V'||';', ';T;') > 0
)
or (
o.OBJECT_NAME like 'XYZ' -- a view name
and o.OBJECT_TYPE = 'VIEW'
and instr(';'||'SY;T;V'||';', ';V;') > 0
)
)
and o.OWNER = c.OWNER
and o.OBJECT_NAME = c.TABLE_NAME
union
select
s.SYNONYM_NAME TABLE_NAME,
c.COLUMN_NAME COL_NAME,
c.DATA_TYPE DT_DRIVER,
substr(c.COLUMN_NAME,1,35) COL_HEADING,
c.COLUMN_ID POS,
case
when C.DATA_TYPE = 'NUMBER' then c.DATA_PRECISION
when C.DATA_TYPE = 'CHAR' then c.CHAR_LENGTH
when C.DATA_TYPE = 'NCHAR' then c.CHAR_LENGTH
when C.DATA_TYPE = 'VARCHAR2' then c.CHAR_LENGTH
when C.DATA_TYPE = 'NVARCHAR2' then c.CHAR_LENGTH
else nvl(c.DATA_PRECISION,c.DATA_LENGTH)
end LONGC,
c.DATA_SCALE SCALEC,
decode(c.NULLABLE,'Y','0','1') COL_MANDATORY
from ALL_TAB_COLUMNS c,
ALL_SYNONYMS s
Where s.OWNER = 'ABC'
and s.SYNONYM_NAME like 'XYZ'
and s.TABLE_OWNER = c.OWNER
and s.TABLE_NAME = c.TABLE_NAME
and instr(';'||'SY;T;V'||';', ';SY;') > 0
-----------------------------------------------------------------------------------

The new explain plan of the sql (which generates 35 rows ) from
autotrace is shown below -

----------------------------------------------------------------------------------
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4645 Card=2 Bytes=
552)

1 0 SORT (UNIQUE) (Cost=4645 Card=2 Bytes=552)
2 1 UNION-ALL
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=
4 Card=1 Bytes=28)

4 3 INDEX (RANGE SCAN) OF 'I_OBJ1' (INDEX (UNIQUE)) (Cos
t=3 Card=1)

5 2 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=
4 Card=1 Bytes=28)

6 5 INDEX (RANGE SCAN) OF 'I_OBJ1' (INDEX (UNIQUE)) (Cos
t=3 Card=1)

7 2 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=
4 Card=1 Bytes=28)

8 7 INDEX (RANGE SCAN) OF 'I_OBJ1' (INDEX (UNIQUE)) (Cos
t=3 Card=1)

9 2 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=
4 Card=1 Bytes=28)

10 9 INDEX (RANGE SCAN) OF 'I_OBJ1' (INDEX (UNIQUE)) (Cos
t=3 Card=1)

11 2 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=
4 Card=1 Bytes=28)

12 11 INDEX (RANGE SCAN) OF 'I_OBJ1' (INDEX (UNIQUE)) (Cos
t=3 Card=1)

13 2 FILTER
14 13 NESTED LOOPS (OUTER) (Cost=1447 Card=1 Bytes=281)
15 14 NESTED LOOPS (OUTER) (Cost=1445 Card=1 Bytes=271)
16 15 NESTED LOOPS (OUTER) (Cost=1444 Card=1 Bytes=267
)

17 16 NESTED LOOPS (Cost=1442 Card=1 Bytes=236)
18 17 NESTED LOOPS (Cost=1441 Card=1 Bytes=214)
19 18 NESTED LOOPS (OUTER) (Cost=1440 Card=1 Byt
es=192)

20 19 NESTED LOOPS (Cost=1439 Card=1 Bytes=163
)

21 20 NESTED LOOPS (Cost=1258 Card=68 Bytes=
8092)

22 21 NESTED LOOPS (Cost=982 Card=1555 Byt
es=102630)

23 22 NESTED LOOPS (Cost=2 Card=1 Bytes=
24)

24 23 TABLE ACCESS (BY INDEX ROWID) OF
'USER$' (CLUSTER) (Cost=1 Card=1 Bytes=12)

25 24 INDEX (UNIQUE SCAN) OF 'I_USER
1' (INDEX (UNIQUE)) (Cost=0 Card=1)

26 23 TABLE ACCESS (BY INDEX ROWID) OF
'USER$' (CLUSTER) (Cost=1 Card=1 Bytes=12)

27 26 INDEX (UNIQUE SCAN) OF 'I_USER
1' (INDEX (UNIQUE)) (Cost=0 Card=1)

28 22 TABLE ACCESS (BY INDEX ROWID) OF '
OBJ$' (TABLE) (Cost=980 Card=1555 Bytes=65310)

29 28 INDEX (RANGE SCAN) OF 'I_OBJ5' (
INDEX (UNIQUE)) (Cost=18 Card=1556)

30 21 TABLE ACCESS (CLUSTER) OF 'COL$' (CL
USTER) (Cost=1 Card=1 Bytes=53)

31 30 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (I
NDEX (CLUSTER)) (Cost=0 Card=1)

32 20 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$
' (TABLE) (Cost=3 Card=1 Bytes=44)

33 32 INDEX (RANGE SCAN) OF 'I_OBJ5' (INDE
X (UNIQUE)) (Cost=2 Card=1)

34 19 TABLE ACCESS (CLUSTER) OF 'COLTYPE$' (CL
USTER) (Cost=1 Card=1 Bytes=29)

35 18 INDEX (RANGE SCAN) OF 'I_USER2' (INDEX (UN
IQUE)) (Cost=1 Card=1 Bytes=22)

36 17 INDEX (RANGE SCAN) OF 'I_USER2' (INDEX (UNIQ
UE)) (Cost=1 Card=1 Bytes=22)

37 16 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE
) (Cost=2 Card=1 Bytes=31)

38 37 INDEX (RANGE SCAN) OF 'I_OBJ3' (INDEX) (Cost
=1 Card=1)

39 15 INDEX (RANGE SCAN) OF 'I_USER2' (INDEX (UNIQUE))
(Cost=1 Card=1 Bytes=4)

40 14 INDEX (RANGE SCAN) OF 'I_HH_OBJ#_INTCOL#' (INDEX)
(Cost=2 Card=1 Bytes=10)

41 13 TABLE ACCESS (CLUSTER) OF 'TAB$' (CLUSTER) (Cost=2 C
ard=1 Bytes=13)

42 41 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (INDEX (CLUSTER))
(Cost=1 Card=1)

43 13 TABLE ACCESS (BY INDEX ROWID) OF 'SUM$' (TABLE) (Cos
t=2 Card=1 Bytes=10)

44 43 INDEX (UNIQUE SCAN) OF 'I_SUM$_1' (INDEX (UNIQUE))
(Cost=1 Card=1)

45 13 NESTED LOOPS (Cost=3 Card=5 Bytes=70)
46 45 INDEX (RANGE SCAN) OF 'I_OBJAUTH1' (INDEX (UNIQUE)
) (Cost=3 Card=5 Bytes=50)

47 45 FIXED TABLE (FULL) OF 'X$KZSRO' (TABLE (FIXED)) (C
ost=0 Card=1 Bytes=4)

48 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=13 Bytes=91)

49 13 TABLE ACCESS (BY INDEX ROWID) OF 'IND$' (CLUSTER) (C
ost=2 Card=1 Bytes=9)

50 49 INDEX (UNIQUE SCAN) OF 'I_IND1' (INDEX (UNIQUE)) (
Cost=1 Card=1)

51 13 NESTED LOOPS
52 51 NESTED LOOPS (Cost=9 Card=1 Bytes=72)
53 52 NESTED LOOPS (Cost=6 Card=1 Bytes=60)
54 53 NESTED LOOPS (Cost=4 Card=1 Bytes=47)
55 54 MERGE JOIN (CARTESIAN) (Cost=3 Card=1 Bytes=
43)

56 55 INDEX (RANGE SCAN) OF 'I_OBJ5' (INDEX (UNI
QUE)) (Cost=3 Card=1 Bytes=39)

57 55 BUFFER (SORT) (Cost=0 Card=42 Bytes=168)
58 57 FIXED TABLE (FULL) OF 'X$KZSRO' (TABLE (
FIXED)) (Cost=0 Card=42 Bytes=168)

59 54 INDEX (RANGE SCAN) OF 'I_USER2' (INDEX (UNIQ
UE)) (Cost=1 Card=1 Bytes=4)

60 53 INDEX (RANGE SCAN) OF 'I_OBJAUTH1' (INDEX (UNI
QUE)) (Cost=2 Card=1 Bytes=13)

61 52 INDEX (RANGE SCAN) OF 'I_DEPENDENCY1' (INDEX (UN
IQUE)) (Cost=2 Card=4)

62 51 TABLE ACCESS (BY INDEX ROWID) OF 'DEPENDENCY$' (TA
BLE) (Cost=3 Card=1 Bytes=12)

63 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=5 Bytes=35)

64 13 NESTED LOOPS (Cost=3 Card=1 Bytes=17)
65 64 INDEX (RANGE SCAN) OF 'I_OBJAUTH1' (INDEX (UNIQUE)
) (Cost=3 Card=1 Bytes=13)

66 64 FIXED TABLE (FULL) OF 'X$KZSRO' (TABLE (FIXED)) (C
ost=0 Card=1 Bytes=4)

67 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=8 Bytes=56)

68 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=13 Bytes=91)

69 13 NESTED LOOPS (Cost=3 Card=3 Bytes=51)
70 69 INDEX (RANGE SCAN) OF 'I_OBJAUTH1' (INDEX (UNIQUE)
) (Cost=3 Card=3 Bytes=39)

71 69 FIXED TABLE (FULL) OF 'X$KZSRO' (TABLE (FIXED)) (C
ost=0 Card=1 Bytes=4)

72 13 NESTED LOOPS (Cost=4 Card=1 Bytes=33)
73 72 NESTED LOOPS (Cost=4 Card=1 Bytes=29)
74 73 TABLE ACCESS (BY INDEX ROWID) OF 'TRIGGER$' (TAB
LE) (Cost=2 Card=1 Bytes=16)

75 74 INDEX (UNIQUE SCAN) OF 'I_TRIGGER2' (INDEX (UN
IQUE)) (Cost=1 Card=1)

76 73 INDEX (RANGE SCAN) OF 'I_OBJAUTH1' (INDEX (UNIQU
E)) (Cost=2 Card=1 Bytes=13)

77 72 FIXED TABLE (FULL) OF 'X$KZSRO' (TABLE (FIXED)) (C
ost=0 Card=1 Bytes=4)

78 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=5 Bytes=35)

79 13 NESTED LOOPS (Cost=3 Card=1 Bytes=17)
80 79 INDEX (RANGE SCAN) OF 'I_OBJAUTH1' (INDEX (UNIQUE)
) (Cost=3 Card=1 Bytes=13)

81 79 FIXED TABLE (FULL) OF 'X$KZSRO' (TABLE (FIXED)) (C
ost=0 Card=1 Bytes=4)

82 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=8 Bytes=56)

83 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=3 Bytes=21)

84 13 NESTED LOOPS
85 84 NESTED LOOPS (Cost=9 Card=1 Bytes=72)
86 85 NESTED LOOPS (Cost=6 Card=1 Bytes=60)
87 86 NESTED LOOPS (Cost=4 Card=1 Bytes=47)
88 87 MERGE JOIN (CARTESIAN) (Cost=3 Card=1 Bytes=
43)

89 88 INDEX (RANGE SCAN) OF 'I_OBJ5' (INDEX (UNI
QUE)) (Cost=3 Card=1 Bytes=39)

90 88 BUFFER (SORT) (Cost=0 Card=42 Bytes=168)
91 90 FIXED TABLE (FULL) OF 'X$KZSRO' (TABLE (
FIXED)) (Cost=0 Card=42 Bytes=168)

92 87 INDEX (RANGE SCAN) OF 'I_USER2' (INDEX (UNIQ
UE)) (Cost=1 Card=1 Bytes=4)

93 86 INDEX (RANGE SCAN) OF 'I_OBJAUTH1' (INDEX (UNI
QUE)) (Cost=2 Card=1 Bytes=13)

94 85 INDEX (RANGE SCAN) OF 'I_DEPENDENCY1' (INDEX (UN
IQUE)) (Cost=2 Card=4)

95 84 TABLE ACCESS (BY INDEX ROWID) OF 'DEPENDENCY$' (TA
BLE) (Cost=3 Card=1 Bytes=12)

96 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=5 Bytes=35)

97 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=13 Bytes=91)

98 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=11 Bytes=77)

99 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=11 Bytes=77)

100 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=11 Bytes=77)

101 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=13 Bytes=91)

102 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=5 Bytes=35)

103 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=11 Bytes=77)

104 13 VIEW (Cost=2 Card=1 Bytes=13)
105 104 FAST DUAL (Cost=2 Card=1)
106 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=5 Bytes=35)

107 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=5 Bytes=35)

108 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=3 Bytes=21)

109 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=5 Bytes=35)

110 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=3 Bytes=21)

111 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=11 Bytes=77)

112 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=5 Bytes=35)

113 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=8 Bytes=56)

114 13 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=11 Bytes=77)

115 13 NESTED LOOPS (Cost=4 Card=1 Bytes=30)
116 115 INDEX (RANGE SCAN) OF 'I_OBJ4' (INDEX) (Cost=3 Car
d=1 Bytes=10)

117 115 INDEX (RANGE SCAN) OF 'I_USER2' (INDEX (UNIQUE)) (
Cost=1 Card=1 Bytes=20)

118 13 NESTED LOOPS (Cost=4 Card=1 Bytes=30)
119 118 INDEX (RANGE SCAN) OF 'I_OBJ4' (INDEX) (Cost=3 Car
d=1 Bytes=10)

120 118 INDEX (RANGE SCAN) OF 'I_USER2' (INDEX (UNIQUE)) (
Cost=1 Card=1 Bytes=20)

121 2 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=
4 Card=1 Bytes=28)

122 121 INDEX (RANGE SCAN) OF 'I_OBJ1' (INDEX (UNIQUE)) (Cos
t=3 Card=1)

123 2 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=
4 Card=1 Bytes=28)

124 123 INDEX (RANGE SCAN) OF 'I_OBJ1' (INDEX (UNIQUE)) (Cos
t=3 Card=1)

125 2 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=
4 Card=1 Bytes=28)

126 125 INDEX (RANGE SCAN) OF 'I_OBJ1' (INDEX (UNIQUE)) (Cos
t=3 Card=1)

127 2 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=
4 Card=1 Bytes=28)

128 127 INDEX (RANGE SCAN) OF 'I_OBJ1' (INDEX (UNIQUE)) (Cos
t=3 Card=1)

129 2 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=
4 Card=1 Bytes=28)

130 129 INDEX (RANGE SCAN) OF 'I_OBJ1' (INDEX (UNIQUE)) (Cos
t=3 Card=1)

131 2 FILTER
132 131 NESTED LOOPS (OUTER) (Cost=3196 Card=1 Bytes=271)
133 132 NESTED LOOPS (OUTER) (Cost=3194 Card=1 Bytes=261)
134 133 NESTED LOOPS (OUTER) (Cost=3193 Card=1 Bytes=257
)

135 134 NESTED LOOPS (OUTER) (Cost=3191 Card=1 Bytes=2
26)

136 135 NESTED LOOPS (Cost=3190 Card=1 Bytes=197)
137 136 NESTED LOOPS (Cost=3189 Card=1 Bytes=144)
138 137 NESTED LOOPS (Cost=3188 Card=1 Bytes=122
)

139 138 HASH JOIN (Cost=2675 Card=193 Bytes=15
440)

140 139 VIEW OF 'ALL_SYNONYMS' (VIEW) (Cost=
2663 Card=193 Bytes=13124)

141 140 SORT (UNIQUE) (Cost=2663 Card=193
Bytes=25833)

142 141 UNION-ALL
143 142 FILTER
144 143 NESTED LOOPS
145 144 NESTED LOOPS (Cost=5 Card=
1 Bytes=105)

146 145 NESTED LOOPS (Cost=4 Car
d=1 Bytes=73)

147 146 NESTED LOOPS (Cost=3 C
ard=1 Bytes=51)

148 147 TABLE ACCESS (BY IND
EX ROWID) OF 'USER$' (CLUSTER) (Cost=1 Card=1 Bytes=12)

149 148 INDEX (UNIQUE SCAN
) OF 'I_USER1' (INDEX (UNIQUE)) (Cost=0 Card=1)

150 147 INDEX (RANGE SCAN) O
F 'I_OBJ5' (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=39)

151 146 INDEX (RANGE SCAN) OF
'I_USER2' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=22)

152 145 INDEX (UNIQUE SCAN) OF '
I_SYN1' (INDEX (UNIQUE)) (Cost=0 Card=1)

153 144 TABLE ACCESS (BY INDEX ROW
ID) OF 'SYN$' (TABLE) (Cost=1 Card=1 Bytes=32)

154 143 FIXED TABLE (FULL) OF 'X$KZS
PR' (TABLE (FIXED)) (Cost=0 Card=13 Bytes=91)

155 143 FILTER
156 155 FILTER
157 156 NESTED LOOPS (Cost=6 Car
d=4 Bytes=348)

158 157 NESTED LOOPS (Cost=4 C
ard=1 Bytes=73)

159 158 NESTED LOOPS (Cost=3
Card=1 Bytes=51)

160 159 TABLE ACCESS (BY I
NDEX ROWID) OF 'USER$' (CLUSTER) (Cost=1 Card=1 Bytes=12)

161 160 INDEX (UNIQUE SC
AN) OF 'I_USER1' (INDEX (UNIQUE)) (Cost=0 Card=1)

162 159 INDEX (RANGE SCAN)
OF 'I_OBJ5' (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=39)

163 158 INDEX (RANGE SCAN) O
F 'I_USER2' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=22)

164 157 INDEX (RANGE SCAN) OF
'I_OBJAUTH1' (INDEX (UNIQUE)) (Cost=2 Card=5 Bytes=70)

165 155 FIXED TABLE (FULL) OF 'X$K
ZSRO' (TABLE (FIXED)) (Cost=0 Card=1 Bytes=4)

166 155 NESTED LOOPS (Cost=4 Card=
1 Bytes=30)

167 166 INDEX (RANGE SCAN) OF 'I
_OBJ4' (INDEX) (Cost=3 Card=1 Bytes=10)

168 166 INDEX (RANGE SCAN) OF 'I
_USER2' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=20)

169 143 NESTED LOOPS (Cost=4 Card=1
Bytes=30)

170 169 INDEX (RANGE SCAN) OF 'I_O
BJ4' (INDEX) (Cost=3 Card=1 Bytes=10)

171 169 INDEX (RANGE SCAN) OF 'I_U
SER2' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=20)

172 142 VIEW OF '_ALL_SYNONYMS_TREE' (
VIEW) (Cost=2656 Card=192 Bytes=25728)

173 172 CONNECT BY (WITH FILTERING)
174 173 FILTER
175 174 COUNT
176 175 FILTER
177 176 HASH JOIN (Cost=2656
Card=192 Bytes=34176)

178 177 TABLE ACCESS (FULL
) OF 'USER$' (CLUSTER) (Cost=12 Card=420 Bytes=5040)

179 177 HASH JOIN (Cost=26
43 Card=192 Bytes=31872)

180 179 INDEX (FULL SCAN
) OF 'I_USER2' (INDEX (UNIQUE)) (Cost=3 Card=420 Bytes=9240)

181 179 NESTED LOOPS (Co
st=2640 Card=192 Bytes=27648)

182 181 HASH JOIN (Cos
t=2063 Card=192 Bytes=20160)

183 182 INDEX (FULL
SCAN) OF 'I_USER2' (INDEX (UNIQUE)) (Cost=3 Card=420 Bytes=9
240)

184 182 HASH JOIN (C
ost=2059 Card=192 Bytes=15936)

185 184 TABLE ACCE
SS (FULL) OF 'SYN$' (TABLE) (Cost=179 Card=76028 Bytes=24328
96)

186 184 HASH JOIN
(Cost=1486 Card=77739 Bytes=3964689)

187 186 TABLE AC
CESS (FULL) OF 'USER$' (CLUSTER) (Cost=12 Card=420 Bytes=504
0)

188 186 TABLE AC
CESS (FULL) OF 'OBJ$' (TABLE) (Cost=1473 Card=77739 Bytes=30
31821)

189 181 TABLE ACCESS (
BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=3 Card=1 Bytes=39)

190 189 INDEX (RANGE
SCAN) OF 'I_OBJ1' (INDEX (UNIQUE)) (Cost=2 Card=1)

191 176 NESTED LOOPS (Cost=4
Card=1 Bytes=30)

192 191 INDEX (RANGE SCAN)
OF 'I_OBJ4' (INDEX) (Cost=3 Card=1 Bytes=10)

193 191 INDEX (RANGE SCAN)
OF 'I_USER2' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=20)

194 176 NESTED LOOPS (Cost=4
Card=1 Bytes=30)

195 194 INDEX (RANGE SCAN)
OF 'I_OBJ4' (INDEX) (Cost=3 Card=1 Bytes=10)

196 194 INDEX (RANGE SCAN)
OF 'I_USER2' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=20)

197 174 FILTER
198 197 TABLE ACCESS (BY INDEX
ROWID) OF 'SYN$' (TABLE) (Cost=2 Card=1 Bytes=32)

199 198 INDEX (UNIQUE SCAN)
OF 'I_SYN1' (INDEX (UNIQUE)) (Cost=1 Card=1)

200 197 FIXED TABLE (FULL) OF
'X$KZSPR' (TABLE (FIXED)) (Cost=0 Card=13 Bytes=91)

201 197 FILTER
202 201 FILTER
203 202 NESTED LOOPS (Cost
=6 Card=4 Bytes=348)

204 203 NESTED LOOPS (Co
st=4 Card=1 Bytes=73)

205 204 NESTED LOOPS (
Cost=3 Card=1 Bytes=51)

206 205 TABLE ACCESS
(BY INDEX ROWID) OF 'USER$' (CLUSTER) (Cost=1 Card=1 Bytes=
12)

207 206 INDEX (UNI
QUE SCAN) OF 'I_USER1' (INDEX (UNIQUE)) (Cost=0 Card=1)

208 205 INDEX (RANGE
SCAN) OF 'I_OBJ5' (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=39)

209 204 INDEX (RANGE S
CAN) OF 'I_USER2' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=22)

210 203 INDEX (RANGE SCA
N) OF 'I_OBJAUTH1' (INDEX (UNIQUE)) (Cost=2 Card=5 Bytes=70)

211 201 FIXED TABLE (FULL) O
F 'X$KZSRO' (TABLE (FIXED)) (Cost=0 Card=1 Bytes=4)

212 201 NESTED LOOPS (Cost=4
Card=1 Bytes=30)

213 212 INDEX (RANGE SCAN)
OF 'I_OBJ4' (INDEX) (Cost=3 Card=1 Bytes=10)

214 212 INDEX (RANGE SCAN)
OF 'I_USER2' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=20)

215 173 HASH JOIN
216 215 CONNECT BY PUMP
217 215 COUNT
218 217 FILTER
219 218 HASH JOIN (Cost=2656
Card=192 Bytes=34176)

220 219 TABLE ACCESS (FULL
) OF 'USER$' (CLUSTER) (Cost=12 Card=420 Bytes=5040)

221 219 HASH JOIN (Cost=26
43 Card=192 Bytes=31872)

222 221 INDEX (FULL SCAN
) OF 'I_USER2' (INDEX (UNIQUE)) (Cost=3 Card=420 Bytes=9240)

223 221 NESTED LOOPS (Co
st=2640 Card=192 Bytes=27648)

224 223 HASH JOIN (Cos
t=2063 Card=192 Bytes=20160)

225 224 INDEX (FULL
SCAN) OF 'I_USER2' (INDEX (UNIQUE)) (Cost=3 Card=420 Bytes=9
240)

226 224 HASH JOIN (C
ost=2059 Card=192 Bytes=15936)

227 226 TABLE ACCE
SS (FULL) OF 'SYN$' (TABLE) (Cost=179 Card=76028 Bytes=24328
96)

228 226 HASH JOIN
(Cost=1486 Card=77739 Bytes=3964689)

229 228 TABLE AC
CESS (FULL) OF 'USER$' (CLUSTER) (Cost=12 Card=420 Bytes=504
0)

230 228 TABLE AC
CESS (FULL) OF 'OBJ$' (TABLE) (Cost=1473 Card=77739 Bytes=30
31821)

231 223 TABLE ACCESS (
BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=3 Card=1 Bytes=39)

232 231 INDEX (RANGE
SCAN) OF 'I_OBJ1' (INDEX (UNIQUE)) (Cost=2 Card=1)

233 139 TABLE ACCESS (FULL) OF 'USER$' (CLUS
TER) (Cost=12 Card=420 Bytes=5040)

234 138 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$
' (TABLE) (Cost=3 Card=1 Bytes=42)

235 234 INDEX (RANGE SCAN) OF 'I_OBJ5' (INDE
X (UNIQUE)) (Cost=2 Card=1)

236 137 INDEX (RANGE SCAN) OF 'I_USER2' (INDEX (
UNIQUE)) (Cost=1 Card=1 Bytes=22)

237 136 TABLE ACCESS (CLUSTER) OF 'COL$' (CLUSTER)
(Cost=1 Card=1 Bytes=53)

238 237 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (INDEX (
CLUSTER)) (Cost=0 Card=1)

239 135 TABLE ACCESS (CLUSTER) OF 'COLTYPE$' (CLUSTE
R) (Cost=1 Card=1 Bytes=29)

240 134 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE
) (Cost=2 Card=1 Bytes=31)

241 240 INDEX (RANGE SCAN) OF 'I_OBJ3' (INDEX) (Cost
=1 Card=1)

242 133 INDEX (RANGE SCAN) OF 'I_USER2' (INDEX (UNIQUE))
(Cost=1 Card=1 Bytes=4)

243 132 INDEX (RANGE SCAN) OF 'I_HH_OBJ#_INTCOL#' (INDEX)
(Cost=2 Card=1 Bytes=10)

244 131 TABLE ACCESS (CLUSTER) OF 'TAB$' (CLUSTER) (Cost=2 C
ard=1 Bytes=13)

245 244 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (INDEX (CLUSTER))
(Cost=1 Card=1)

246 131 NESTED LOOPS (Cost=3 Card=5 Bytes=70)
247 246 INDEX (RANGE SCAN) OF 'I_OBJAUTH1' (INDEX (UNIQUE)
) (Cost=3 Card=5 Bytes=50)

248 246 FIXED TABLE (FULL) OF 'X$KZSRO' (TABLE (FIXED)) (C
ost=0 Card=1 Bytes=4)

249 131 FIXED TABLE (FULL) OF 'X$KZSPR' (TABLE (FIXED)) (Cos
t=0 Card=13 Bytes=91)

250 131 NESTED LOOPS (Cost=4 Card=1 Bytes=30)
251 250 INDEX (RANGE SCAN) OF 'I_OBJ4' (INDEX) (Cost=3 Car
d=1 Bytes=10)

252 250 INDEX (RANGE SCAN) OF 'I_USER2' (INDEX (UNIQUE)) (
Cost=1 Card=1 Bytes=20)

Statistics
----------------------------------------------------------
201 recursive calls
0 db block gets
6019844 consistent gets
1045 physical reads
0 redo size
2578 bytes sent via SQL*Net to client
755 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
35 rows processed
----------------------------------------------------------------------------------

I remember that the sql was much faster earlier (with a cost of 950 or
so) and am wondering if there is any specific thing that I can do to
reduce the cost.

I have tried a DBMS_STATS.GATHER_DICTIONARY_STATS for this sql and I
found that the cost had jumped from 4645 to 8700! I tried to execute
dbms_stats.gather_fixed_objects_stats and
dbms_stats.gather_schema_stats('SYS', gather_fixed=>TRUE) but the cost
didn't come down.

Any suggestion on the cost reduction will be appreciated.

Thanks,

From: Charles Hooper on 25 Jun 2008 14:39

---

On Jun 25, 1:53 pm, saurangshu <saurang...(a)gmail.com> wrote:
> Hi,
>
> We are using certain sql to get the column information for a group of
> tables (in Oracle 10gR2 database). The sql is based on the underline
> Oracle data dictionary tables/views and it is taking a huge amount of
> time to execute only after the recent restoration of our development
> database.

I see at least one Cartesian Merge Join in the plan that you posted.
You might want to look at this thread, which appears to be related:
"A potential bug (infinite loop) in Oracle: querying v$access"
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/e0599d3e043fc199

As Mark mentions, cost alone should not be the only consideration with
a query, but the plan might be important. The query that you posted,
when executed on one of my databases, produces the following DBMS
Xplan output (note that there is no Cartesian Merge Join in the plan):
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation |
Name | Starts | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------
| 1 | SORT UNIQUE
| | 1 | 0 |00:00:00.01 | 3 |
| 2 | UNION-ALL
| | 1 | 0 |00:00:00.01 | 3 |
|* 3 | FILTER
| | 1 | 0 |00:00:00.01 | 1 |
| 4 | NESTED LOOPS OUTER
| | 1 | 0 |00:00:00.01 | 1 |
| 5 | NESTED LOOPS OUTER
| | 1 | 0 |00:00:00.01 | 1 |
| 6 | NESTED LOOPS OUTER
| | 1 | 0 |00:00:00.01 | 1 |
| 7 | NESTED LOOPS OUTER
| | 1 | 0 |00:00:00.01 | 1 |
| 8 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 9 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 10 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 11 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 12 | TABLE ACCESS BY INDEX ROWID | USER
$ | 1 | 0 |00:00:00.01 | 1 |
|* 13 | INDEX UNIQUE SCAN |
I_USER1 | 1 | 0 |00:00:00.01 | 1 |
| 14 | TABLE ACCESS BY INDEX ROWID | USER
$ | 0 | 0 |00:00:00.01 | 0 |
|* 15 | INDEX UNIQUE SCAN |
I_USER1 | 0 | 0 |00:00:00.01 | 0 |
| 16 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 |
|* 17 | INDEX RANGE SCAN |
I_OBJ2 | 0 | 0 |00:00:00.01 | 0 |
| 18 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 |
|* 19 | INDEX RANGE SCAN |
I_OBJ2 | 0 | 0 |00:00:00.01 | 0 |
|* 20 | TABLE ACCESS CLUSTER | COL
$ | 0 | 0 |00:00:00.01 | 0 |
|* 21 | INDEX UNIQUE SCAN |
I_OBJ# | 0 | 0 |00:00:00.01 | 0 |
|* 22 | TABLE ACCESS CLUSTER | COLTYPE
$ | 0 | 0 |00:00:00.01 | 0 |
|* 23 | INDEX RANGE SCAN |
I_HH_OBJ#_INTCOL# | 0 | 0 |00:00:00.01 | 0 |
|* 24 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 |
|* 25 | INDEX RANGE SCAN |
I_OBJ3 | 0 | 0 |00:00:00.01 | 0 |
| 26 | TABLE ACCESS CLUSTER | USER
$ | 0 | 0 |00:00:00.01 | 0 |
|* 27 | INDEX UNIQUE SCAN |
I_USER# | 0 | 0 |00:00:00.01 | 0 |
|* 28 | TABLE ACCESS BY INDEX ROWID | SUM
$ | 0 | 0 |00:00:00.01 | 0 |
|* 29 | INDEX UNIQUE SCAN | I_SUM
$_1 | 0 | 0 |00:00:00.01 | 0 |
|* 30 | TABLE ACCESS BY INDEX ROWID | IND
$ | 0 | 0 |00:00:00.01 | 0 |
|* 31 | INDEX UNIQUE SCAN |
I_IND1 | 0 | 0 |00:00:00.01 | 0 |
|* 32 | TABLE ACCESS BY INDEX ROWID | OBJAUTH
$ | 0 | 0 |00:00:00.01 | 0 |
| 33 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 34 | FIXED TABLE FULL | X
$KZSRO | 0 | 0 |00:00:00.01 | 0 |
|* 35 | INDEX RANGE SCAN |
I_OBJAUTH2 | 0 | 0 |00:00:00.01 | 0 |
|* 36 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 37 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 38 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 39 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 40 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 41 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 42 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 43 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 44 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 45 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 46 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 47 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 48 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 49 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 50 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
| 51 | VIEW
| | 0 | 0 |00:00:00.01 | 0 |
| 52 | FAST DUAL
| | 0 | 0 |00:00:00.01 | 0 |
|* 53 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 54 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 55 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 56 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 57 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 58 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 59 | TABLE ACCESS CLUSTER | TAB
$ | 0 | 0 |00:00:00.01 | 0 |
|* 60 | INDEX UNIQUE SCAN |
I_OBJ# | 0 | 0 |00:00:00.01 | 0 |
| 61 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 62 | FIXED TABLE FULL | X
$KZSRO | 0 | 0 |00:00:00.01 | 0 |
|* 63 | INDEX RANGE SCAN |
I_OBJAUTH2 | 0 | 0 |00:00:00.01 | 0 |
|* 64 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
|* 65 | FILTER
| | 1 | 0 |00:00:00.01 | 2 |
| 66 | NESTED LOOPS OUTER
| | 1 | 0 |00:00:00.01 | 2 |
| 67 | NESTED LOOPS OUTER
| | 1 | 0 |00:00:00.01 | 2 |
| 68 | NESTED LOOPS OUTER
| | 1 | 0 |00:00:00.01 | 2 |
| 69 | NESTED LOOPS OUTER
| | 1 | 0 |00:00:00.01 | 2 |
| 70 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 2 |
| 71 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 2 |
| 72 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 2 |
| 73 | VIEW |
ALL_SYNONYMS | 1 | 0 |00:00:00.01 | 2 |
| 74 | SORT UNIQUE
| | 1 | 0 |00:00:00.01 | 2 |
| 75 | UNION-ALL
| | 1 | 0 |00:00:00.01 | 2 |
|* 76 | FILTER
| | 1 | 0 |00:00:00.01 | 1 |
| 77 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 78 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 79 | TABLE ACCESS BY INDEX ROWID | USER
$ | 1 | 0 |00:00:00.01 | 1 |
|* 80 | INDEX UNIQUE SCAN |
I_USER1 | 1 | 0 |00:00:00.01 | 1 |
|* 81 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 |
|* 82 | INDEX RANGE SCAN |
I_OBJ2 | 0 | 0 |00:00:00.01 | 0 |
| 83 | TABLE ACCESS BY INDEX ROWID | SYN
$ | 0 | 0 |00:00:00.01 | 0 |
|* 84 | INDEX UNIQUE SCAN |
I_SYN1 | 0 | 0 |00:00:00.01 | 0 |
|* 85 | FILTER
| | 0 | 0 |00:00:00.01 | 0 |
|* 86 | FILTER
| | 0 | 0 |00:00:00.01 | 0 |
| 87 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 88 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 89 | TABLE ACCESS BY INDEX ROWID | USER
$ | 0 | 0 |00:00:00.01 | 0 |
|* 90 | INDEX UNIQUE SCAN |
I_USER1 | 0 | 0 |00:00:00.01 | 0 |
| 91 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 |
|* 92 | INDEX RANGE SCAN |
I_OBJ2 | 0 | 0 |00:00:00.01 | 0 |
|* 93 | INDEX RANGE SCAN |
I_OBJAUTH1 | 0 | 0 |00:00:00.01 | 0 |
|* 94 | FIXED TABLE FULL | X
$KZSRO | 0 | 0 |00:00:00.01 | 0 |
|* 95 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
| 96 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 97 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 98 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 99 | TABLE ACCESS BY INDEX ROWID | USER
$ | 1 | 0 |00:00:00.01 | 1 |
|*100 | INDEX UNIQUE SCAN |
I_USER1 | 1 | 0 |00:00:00.01 | 1 |
| 101 | VIEW |
_ALL_SYNONYMS_TREE | 0 | 0 |00:00:00.01 | 0 |
|*102 | CONNECT BY WITHOUT FILTERING
| | 0 | 0 |00:00:00.01 | 0 |
|*103 | FILTER
| | 0 | 0 |00:00:00.01 | 0 |
| 104 | COUNT
| | 0 | 0 |00:00:00.01 | 0 |
| 105 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 106 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 107 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 108 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 109 | TABLE ACCESS FULL | USER
$ | 0 | 0 |00:00:00.01 | 0 |
| 110 | TABLE ACCESS BY INDEX ROWID| OBJ
$ | 0 | 0 |00:00:00.01 | 0 |
|*111 | INDEX RANGE SCAN |
I_OBJ2 | 0 | 0 |00:00:00.01 | 0 |
| 112 | TABLE ACCESS BY INDEX ROWID | SYN
$ | 0 | 0 |00:00:00.01 | 0 |
|*113 | INDEX UNIQUE SCAN |
I_SYN1 | 0 | 0 |00:00:00.01 | 0 |
| 114 | TABLE ACCESS BY INDEX ROWID | USER
$ | 0 | 0 |00:00:00.01 | 0 |
|*115 | INDEX UNIQUE SCAN |
I_USER1 | 0 | 0 |00:00:00.01 | 0 |
|*116 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 |
|*117 | INDEX RANGE SCAN |
I_OBJ2 | 0 | 0 |00:00:00.01 | 0 |
|*118 | FILTER
| | 0 | 0 |00:00:00.01 | 0 |
| 119 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 120 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 121 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
|*122 | TABLE ACCESS BY INDEX ROWID | SYN
$ | 0 | 0 |00:00:00.01 | 0 |
|*123 | INDEX UNIQUE SCAN |
I_SYN1 | 0 | 0 |00:00:00.01 | 0 |
| 124 | TABLE ACCESS BY INDEX ROWID | USER
$ | 0 | 0 |00:00:00.01 | 0 |
|*125 | INDEX UNIQUE SCAN |
I_USER1 | 0 | 0 |00:00:00.01 | 0 |
| 126 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 |
|*127 | INDEX RANGE SCAN |
I_OBJ2 | 0 | 0 |00:00:00.01 | 0 |
|*128 | INDEX RANGE SCAN |
I_OBJAUTH1 | 0 | 0 |00:00:00.01 | 0 |
|*129 | FIXED TABLE FULL | X
$KZSRO | 0 | 0 |00:00:00.01 | 0 |
| 130 | COUNT
| | 0 | 0 |00:00:00.01 | 0 |
| 131 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 132 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 133 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 134 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 135 | TABLE ACCESS FULL | USER
$ | 0 | 0 |00:00:00.01 | 0 |
| 136 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 |
|*137 | INDEX RANGE SCAN |
I_OBJ2 | 0 | 0 |00:00:00.01 | 0 |
| 138 | TABLE ACCESS BY INDEX ROWID | SYN
$ | 0 | 0 |00:00:00.01 | 0 |
|*139 | INDEX UNIQUE SCAN |
I_SYN1 | 0 | 0 |00:00:00.01 | 0 |
| 140 | TABLE ACCESS BY INDEX ROWID | USER
$ | 0 | 0 |00:00:00.01 | 0 |
|*141 | INDEX UNIQUE SCAN |
I_USER1 | 0 | 0 |00:00:00.01 | 0 |
|*142 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 |
|*143 | INDEX RANGE SCAN |
I_OBJ2 | 0 | 0 |00:00:00.01 | 0 |
|*144 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 |
|*145 | INDEX UNIQUE SCAN |
I_OBJ1 | 0 | 0 |00:00:00.01 | 0 |
| 146 | TABLE ACCESS BY INDEX ROWID | SYN
$ | 0 | 0 |00:00:00.01 | 0 |
|*147 | INDEX UNIQUE SCAN |
I_SYN1 | 0 | 0 |00:00:00.01 | 0 |
| 148 | TABLE ACCESS BY INDEX ROWID | USER
$ | 0 | 0 |00:00:00.01 | 0 |
|*149 | INDEX UNIQUE SCAN |
I_USER1 | 0 | 0 |00:00:00.01 | 0 |
| 150 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 |
|*151 | INDEX RANGE SCAN |
I_OBJ2 | 0 | 0 |00:00:00.01 | 0 |
|*152 | TABLE ACCESS CLUSTER | COL
$ | 0 | 0 |00:00:00.01 | 0 |
|*153 | INDEX UNIQUE SCAN |
I_OBJ# | 0 | 0 |00:00:00.01 | 0 |
|*154 | TABLE ACCESS CLUSTER | COLTYPE
$ | 0 | 0 |00:00:00.01 | 0 |
|*155 | INDEX RANGE SCAN |
I_HH_OBJ#_INTCOL# | 0 | 0 |00:00:00.01 | 0 |
|*156 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 |
|*157 | INDEX RANGE SCAN |
I_OBJ3 | 0 | 0 |00:00:00.01 | 0 |
| 158 | TABLE ACCESS CLUSTER | USER
$ | 0 | 0 |00:00:00.01 | 0 |
|*159 | INDEX UNIQUE SCAN |
I_USER# | 0 | 0 |00:00:00.01 | 0 |
|*160 | TABLE ACCESS CLUSTER | TAB
$ | 0 | 0 |00:00:00.01 | 0 |
|*161 | INDEX UNIQUE SCAN |
I_OBJ# | 0 | 0 |00:00:00.01 | 0 |
| 162 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 163 | FIXED TABLE FULL | X
$KZSRO | 0 | 0 |00:00:00.01 | 0 |
|*164 | INDEX RANGE SCAN |
I_OBJAUTH2 | 0 | 0 |00:00:00.01 | 0 |
|*165 | FIXED TABLE FULL | X
$KZSPR | 0 | 0 |00:00:00.01 | 0 |
---------------------------------------------------------------------------------------------------------------------------

You might also consider switching to a UNION ALL, rather than using a
UNION.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

 | 
Pages: 1
Prev: Please DON'T Vote for AMIN!! EVER!!!!
Next: MWA question