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..
Have you looked at setting up a test system to determine if stats will help. You could setup up a system from an import. If you want to gen stats without effecting the optimizer change the optimizer_mode to rule.
Whether or not cbo or rule is faster depends on a lot of factors including: version of Oracle including which patches are installed, how you generate stats, how the tables are layed out in your database and what kinds of hints if any you use. Setting up a test system is the only way to know for certain without effecting your prod db's performance.
Oracle Corporation strongly advises the use of cost-based optimization. The rule-based optimization is available for backward compatibility with legacy applications and will be deprecated in a future release.