Cost-Based or Rule-Based??.. That's the question..
This kinda problem will look familiar to you guys..
I was looking for chained rows on the most used tables (Not all) in our DB, so i analyzed them to get some info.
The result of this process (besides the info off course) was a Low DB Performance in querys that were working fine before this process (and use at least one of the tables that were analyzed).
I had to delete the statistics to recover the previous performance.
This behavior was expected because the Optimizer Mode Parameter was set to CHOOSE in our DB, and the query involved tables with stats and tables without them. This resulted in sub-optimal performance because Oracle enabled Cost-Based Optimization and guessed stats for tables that actually donít have them. However Queryís performance was fine in Rule-Based Optimization.
So... I read somewhere that Cost-Based Optimization is better than Rule Based Optimization, and if this is true i would like to take benefit on it.
Is this True??.. (i donít know really)
Or depends on certain conditions (If it does.. Wich ones??)
The fact is that i donít want to compute statistics for all my tables to figure this out... because Low Performance in Production DB right now is not an option. Maybe the appplication could not be prepared for this kind of optimization and will have Low Performance anyway after all this work..
Maybe you can help with some advices...
Any help would be appreciated..