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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts:

  • » Thank You Wolfgang