We have CBO Cost based Optimizer in oracle which will optimize the query. Do we need to do any settings in oracle in order to enable this?
Thank you,
Regards
BI DWH BALA
asked
Cost based optimizer (CBO) question?
According to this, if you're on 10g, the cost-based optimizer is the only optimizer:
so it's "enabled" by default.
I also found this, referring to the 8i release:
which says:
Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and will be desupported in future releases.
and
Cost Based Optimizer (CBO) - This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources.
So, it seems that you will use CBO in earlier releases than 10g, if you gather stats.
This is really good feature. Once we collect statistics, oracle will take care of performance optimization. This sounds good and reduce the users responsibility in tuning part. And the links are good. Thanks for sharing with us. Thanks once again.
- The optimizer mode can be set at the system-wide level, for an individual session, or for a specific SQL statement:
alter system set optimizer_mode=first_rows_10; alter session set optimizer_goal = all_rows; select /*+ first_rows(100) */ from table_nme;
OPTIMIZER_MODES
- first_rows
- first_rows_1
- first_rows_10
- first_rows_100
- first_rows_1000
- all_rows
- choose
- rule
Resources
