COST vs Rule based optimizer

[sonaliak]

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?

How do I know what we are using here ?

thanks
Sonali

[Dave_A]

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.

HTH

David.

[sonaliak]

Thanks a lot David.
Just one more question you said it will use cost based optimizer if atleast one of the objects in a query has statistics gathered.. How do I do this ???

thanks again

[mbaliga]

Hi,

Use "analyze table" command. You can compute statistics for that table OR do estimate statistics if the tablle has many rows.

Baliga

[Dave_A]

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 ;-)

HTH

David.

[sonaliak]

Yes, I did infact use the same dbms_utility to analyze schema.
execute dbms_utility.analyze_schema('OWNER', 'COMPUTE');
But my problem is even after I ran that

select * from v$parameter where name = 'optimizer_mode'
still shows choose !

How do I know now that it is using cost based optimizer ?

thanks

[GreatDBA]

Don't Worry about the Choose that is Default and it is best to have.

If your tables are Analyzed, Oracle will use the Cost based Otherwise Rule based.

Better to Have Choose as it works for Both...
Cheers.

[macswell]

A few points:

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.

Tim

[wjramsey]

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).

Joe