Optimizer Mode - Can I force Cost Based mode

[jcshepp]

One of our customers wants me to set their database to use Cost based optimizer mode only. At the moment it is set to Choose. However , according to my Oracle DBA books , my choices for optimizer mode are :- rule,choose,first_rows and all-rows.

Can a datbase be set to only use COST based optimzer and never use rule based ?

The database tables are analysed weekly . version is 8.0.6.

 

[carp]

As your documentation would suggest, there is no COST option - but first_rows and all_rows are the CBO selections. They determine what the CBO is trying to accomplish - the fastest response time to get the first row returned to the user or the fastest response time to get all rows returned to the user.

 

[MPeccorini]

I would strongly suggest you leave the optimizer_mode in CHOOSE. FIRST_ROWS and ALL_ROWS might lead the optimizer to very different execution plans causing several problems.

For example, let's say you use FIRST_ROWS: in this case your lists of records will work fine and you will be allowed to scroll your cursor with a fine response time. However, your reports will take longer because optimization will be made so the first rows are returned faster eventhough the complete result is returned slower (and belive me it can be VERY SLOWER).

On the other hand, lets say you use ALL_ROWS: in this case you fix the reports issue I described. However, you will have to wait for the entire cursor to be processed before seeing the first few rows, so your lists of records will take an eternity to appear the first time.

What you should do is use hints in your SELECT statements to inform the optimizer how each statement's preferred goal is. For example: SELECT /*+ FIRST_ROWS */ my_column FROM my_table ...

Best regards,

Mauricio Peccorini

 

[carp]

I would second Mauricio's recommendation. Leave it at CHOOSE. If you want to force Oracle to use CBO, then make sure you have statistics generated for your tables. So far as I know, if the statistics exist and the optimizer is set to CHOOSE, you have to explicitly tell Oracle to use RBO; otherwise, CBO will be used.