Huge Execution Plan Cost Change For Fixed Tables
From: saurangshu <saurangshu_at_gmail.com>
Date: Wed, 25 Jun 2008 10:53:04 -0700 (PDT)
Message-ID: <e6f49ad0-552d-48f5-b363-dd4439232f4a@i18g2000prn.googlegroups.com>
select
decode(c.NULLABLE,'Y','0','1') COL_MANDATORY from ALL_TAB_COLUMNS c,
and o.OBJECT_NAME = c.TABLE_NAME
union
select
decode(c.NULLABLE,'Y','0','1') COL_MANDATORY from ALL_TAB_COLUMNS c,
Execution Plan
Date: Wed, 25 Jun 2008 10:53:04 -0700 (PDT)
Message-ID: <e6f49ad0-552d-48f5-b363-dd4439232f4a@i18g2000prn.googlegroups.com>
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, Received on Wed Jun 25 2008 - 12:53:04 CDT