Here one more Parameter of the Week session (POW) 🙂
Optimization is a task that can be set on a database-wide level in the Oracle database configuration parameter OPTIMIZER_MODE. By default its value is ALL_ROWS.
Before 10g (9i at least), CHOOSE would allow you to use cost based optimization if you gather statistics on your database… otherwise rule-based optimization is be used.
Settings of CHOOSE and RULE for the OPTMIZER_MODE parameter are now deprecated in 10g (no more rule-based optimization).
In 10g, ALL_ROWS is the default value for the OPTIMIZER_MODE… You can set FIRST_ROWS_n, where “n” is the number of rows retrive. For instance FIRST_ROWS_1000 would make short transactions (that return few rows) perform better but full table scans and full index could be hurt with this set!
You can set OPTIMIZER_MODE by session:
ALTER SESSION SET OPTIMIZER_MODE=RULE;
Be sure that your statistics are updated!! This is critical for database performance.