Re: Proper Optimization selection.

Asif H. rajwani wrote:
>
> Hi,
> I have couple of questions about oracle optimization.
>
> 1. What kind of optimizer is better to use Rule based or Cost based.
> When one would use the rule based or cost based optimizer OR is it wise
> to always use the cost based optimizer.

Generally, you want to use the Cost based optimizer. The reason for this is that the Oracle engine performs some very sophisticated SQL analysis with the cost-based optimizer that simply is not available with rule-based. For example, there may be an occassion where a SQL command can be best executed with a full table scan rather than using an index (this all depends on the size of the table, the columns being queried, whether or not an index is present, the size of such an index, etc). In this scenario, the cost-based optimizer may opt for a full table scan whereas the rule-based will NEVER perform a full table scan before using an index.

But this is not an absolute answer. If you are using a prepackaged application, such as Oracle Financials, chances are you will need to use the rule-based optimizer. Some applications have existed since before Oracle developed the cost-based method of optimization and have thus been tuned to use the rules-based most effectively. If this is you, you must consult your application's documentation to see what optimizer they recommend.

> 2. If one uses the cost based optimizer and runs the "anlyze table
> table_name compute statistics" for all the table in the database
> periodically then is there any need to adjust the sequence of tables in
> the from statement to give hints to the optimizer which table to use as
> the driver.

You probably only need to run the table analysis and leave your SQL statements alone. Remember that changing your application's SQL will result in more frequent re-parsing causing more misses on the SQL cache. Unless you radically change your table structure, re-arranging your 'from' clause should be unnecessary.

However, remember that if you are using hints in your SQL, Oracle will ignore the cost-based optimizer's execution suggestion and obey what the hint tells it to do.

Hope this helps,

Sven Barzanallana
Lead Oracle DBA
Greyhound Lines, Inc. Received on Mon Jul 07 1997 - 00:00:00 CDT