| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Why CBO choose the wrong plan?
Hi
Thank you for all response.
show parameter optimizer
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_dynamic_sampling integer 2 optimizer_features_enable string 10.2.0.2 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string ALL_ROWS optimizer_secure_view_merging boolean TRUE
I was wrong about CPU costing. I mean system statistics isn't gathered
by
dbms_stats.gather_system_stats().
It looks more like optimizer handling it wrong. If I limit the query to one partition, the cost drops dramatically, then CBO use the index without hint.
TBL_FIN_ACC_STAT_SUMM.SETTLED_DT >= to_date('20070601','yyyymmdd')
and
TBL_FIN_ACC_STAT_SUMM.SETTLED_DT < to_date('20070602','yyyymmdd')
|* 4 | INDEX RANGE SCAN | ACC_STAT_ACCOUNT_ID
| 4 | | 11 (0)| 1557 | 1557 |
TBL_FIN_ACC_STAT_SUMM.SETTLED_DT >= to_date('20070602','yyyymmdd')
and
TBL_FIN_ACC_STAT_SUMM.SETTLED_DT < to_date('20070603','yyyymmdd')
|* 4 | INDEX RANGE SCAN | ACC_STAT_ACCOUNT_ID
| 4 | | 11 (0)| 1558 | 1558 |
The index is normal B-tree. These are information about histogram. all_part_col_statistics
PARTITION_NAME NUM_DISTINCT DENSITY NUM_BUCKETSSAMPLE_SIZE HISTOGRAM
------------------------------ ------------ ---------- ----------- ----------- --------------- DY01062007 29746 .00155521 64 177688 HEIGHT BALANCED DY02062007 44599 .00141844 66 369367 HEIGHT BALANCED DY03062007 28671 .000034878 1 222139 NONE
I notice that some partitions have histograms, some don't . I test the query against different scenarios, the result is the same--one partition use Index, two use full table scan.
ALL_PART_HISTOGRAMS
PARTITION_NAME BUCKET_NUMBER ENDPOINT_VALUE
-------------------- ------------- -------------- DY01062007 0 12 DY01062007 1 2438 DY01062007 2 4723 DY01062007 3 6936 DY01062007 4 9851 DY01062007 5 18469 DY01062007 6 24152 DY01062007 7 33436 DY01062007 8 33969 DY01062007 9 40186 DY01062007 10 50064 DY01062007 11 61182 DY01062007 12 70438 DY01062007 13 94597 DY01062007 14 109383 DY01062007 15 119272 DY01062007 16 139571 DY01062007 17 167205 DY01062007 18 196404 DY01062007 19 240049 DY01062007 20 264111 DY01062007 21 285909 DY01062007 22 315491 DY01062007 23 337693 DY01062007 24 361196 DY01062007 25 379769 DY01062007 26 407067 DY01062007 27 434499 DY01062007 28 456099 DY01062007 29 477096 DY01062007 30 515661 DY01062007 31 533712 DY01062007 32 561237 DY01062007 33 597972 DY01062007 34 636504 DY01062007 35 643524 DY01062007 36 676615 DY01062007 37 698278 DY01062007 38 714163 DY01062007 39 717435 DY01062007 40 737670 DY01062007 41 774714 DY01062007 42 830257 DY01062007 43 863614 DY01062007 44 917942 DY01062007 45 964810 DY01062007 46 1004785 DY01062007 47 1043736 DY01062007 48 1073780 DY01062007 49 1087548 DY01062007 50 1103344 DY01062007 51 1131096 DY01062007 52 1144839 DY01062007 53 1165219 DY01062007 54 1170960 DY01062007 55 1176436 DY01062007 56 1176440 DY01062007 57 1203045 DY01062007 58 1221617 DY01062007 59 1246963 DY01062007 60 1270949 DY01062007 61 1294947 DY01062007 62 1323754 DY01062007 63 1343250 DY01062007 64 1355307
ALL_TAB_HISTOGRAMS
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 2
1 497
3 1377
4 1607
5 1617
6 1703
7 2372
8 2931
9 3395
10 3951
11 4200
12 4568
13 4734
14 5241
15 5772
16 6069
17 6436
19 6936
20 7160
21 8020
22 8713
23 9362
24 9917
25 10914
26 12149
27 12712
28 13564
29 14295
30 14994
31 16640
32 17821
33 19381
34 20590
35 21290
36 22068
37 23287
38 23491
39 23959
40 24627
41 25376
42 25872
43 26734
44 27532
45 28397
46 29231
50 30447
51 31481
52 32939
53 33588
54 33670
56 33969
57 34094
58 34671
59 34871
60 36328
61 37640
62 39303
63 40695
64 42035
65 42635
66 43458
67 44456
68 45464
69 46798
70 48054
71 48845
72 49952
73 51021
74 52328
75 53405
76 54585
77 56145
78 56938
79 58087
80 59718
81 60682
82 61978
83 63367
84 64719
85 66668
86 68088
87 69292
88 70723
89 73303
90 78191
91 84227
92 86239
93 89143
94 92782
95 94535
96 96087
97 98823
98 99800
99 102577
100 104981
101 108160
103 109383
104 112046
105 116248
106 120529
107 123628
108 125769
109 127682
110 130113
111 135758
112 139614
113 144047
114 147979
115 151049
116 152856
117 157403
118 160734
119 165982
120 169803
121 173696
122 176167
123 180177
124 185937
125 191452
126 196214
127 202861
128 208524
129 215262
130 226761
131 235032
132 241083
133 245519
134 250996
135 256717
136 261079
137 265024
138 269367
139 272862
140 278122
141 282538
142 285771
143 290122
144 296274
145 300501
146 306905
147 315594
148 321314
149 324401
150 325379
151 329735
152 334707
153 339253
154 343807
155 349126
156 355374
157 358084
158 363040
159 366866
160 370942
161 375157
162 379145
163 383531
164 388102
165 390991
166 394236
167 397970
168 402542
169 408211
170 410540
171 413430
172 417735
173 421673
174 424582
175 428529
176 432425
177 432932
178 433018
179 436208
180 438512
182 442282
183 442720
184 445385
185 445491
186 449055
187 454342
188 457231
189 461183
190 464215
191 469668
192 478174
193 485900
194 492515
195 506721
196 515272
197 517963
198 521720
199 526449
200 543198
201 550898
202 558708
203 560921
204 568887
205 577207
206 590805
207 597156
208 608497
209 619209
210 624123
211 630598
212 636507
213 642897
214 647320
215 655817
216 666977
217 672337
218 678740
219 687114
220 692908
221 701304
222 709558
224 714163
225 716761
226 720073
227 727376
228 734365
229 744394
230 752557
231 766996
232 775124
233 788893
234 829904
235 844723
236 863612
237 879836
238 899105
239 922605
240 955497
241 987731
242 1002428
243 1026977
244 1046019
245 1060080
246 1082816
247 1105383
248 1131096
249 1151836
250 1166104
251 1176436
252 1202273
253 1236033
254 1327172
Thanks,
Bin
Received on Sun Jul 01 2007 - 21:53:15 CDT
 |
 |