One of our clients, had a certain report that was bringing the database to a complete halt for 10 minutes while it ran. The dba changed the instance from rule based optimization to cost based and suddenly the report ran in a minute or so and did not monopolize the resources.
I thought that cost based was the default, but am I wrong?
select * from v$parameter where name = 'optimizer_mode'
Check the value column. It will be either Rule, Cost or Choose.
Rule and Cost speak for themselves.
Choose (the default value) will use the Cost Based Optmizer when at least one of the objects in the query has statistics gathered for it. Otherwise the rule based optimizer is used.
You can also use the DBMS_STATS.ANALYZE_SCHEMA packaged procedure to analyze all the objects in a particular user's schema. (Sorry, I can't recall all the parameter settings required for this procedure off the top of my head, but it should point you in the right direction when you have a look at the documentation at least ;-)
1. I agree that CHOOSE is the appropriate optimizer mode.
2. Regenerate your statistics on a regular basis. How often depends on your transaction volume; I do it twice a week in one production database, once a week in the other. I use a cron job to compute (not estimate) the statistics during off-hours.
3. Sometimes RULE works better for a particular query, so remember that you can use optimizer hints on an individual basis. Experiment (with sql_trace true) to determine which method is better for problem queries.
4. I have had luck with some problem queries by *deleting* statistics from specific tables. I'm not sure why this works, but it does.
It works because in the absence of statistics, Oracle uses the rule-based optimizer. In your case, the CBO must have picked a less-appropriate path to your data than that which was taken by the RBO. It may have been you were doing a full table scan before but with the RBO used an index which is preferred by RBO (fts is the last access path chosen after all other possibilities have been exhausted).
Senior Database Administrator