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.