| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Thank You Wolfgang
Recently the NOUG DBA SIG had Wolfgang Breitling, from the list, in for = a talk on the "Fallacies of the Cost Based Optimizer". I will admit = that even for a techie like me some of it was a little hard to phatom, = but a nights sleep on the subject did clear the waters. This morning = I'm faced with a performance problem in which it is very obvious that = the CBO is selecting the wrong access part to a table, consequently = resulting in a general performance slowdown and p?**y customers. A look = at the statement being executed and remembering Wolfgang's presentation = gave me the answer. The statement was looking for "division =3D -1". = Now the column is in several indexes as the first column thereof, but = more importantly the statement is patently not true. A look at the = histograms on the index indicates that the low value is 0. No wonder = the CBO was taking the path it did. Solution, add a row where division = =3D -1, redo the statistics including the histogram, delete the bogus = row and CBO then takes the right path. Result, same query takes 100% = less time to execute, down to 0.000,001 seconds from 3 minutes. And = since this thing gets run several thousand times a day(2000+ as of right = now), we're talking real time now.
Therefore, Thanks Wolfgang, you saved my bacon.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
 |
 |