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..
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.
There's no question. Get used to CBO as it will be the ONLY optimizer available in the future.
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.
Oracle 10i will not have the Rule-Based Optimizer...
OCP 8i, 9i DBA
Looks like i'll have to read a little more about CBO..
Anyone can suggest a good source of information for Query's Optimization, Hints & Tips or stuff like that ??
Thanks in advance
tahiti.oracle.com is a good start, list of books, performance tuning guide and reference
Thank you very much to all who replied..
Click Here to Expand Forum to Full Width