When is cost based optimization triggered?

It's important to have statistics on all tables for the CBO (Cost Based
Optimizer) to work correctly. If one table involved in a statement does not
have statistics, Oracle has to revert to rule-based optimization for that statement.
So you really want for all tables to have statistics right away; it won't
help much to just have the larger tables analyzed.

Generally, the CBO can change the execution plan when you:

• Change statistics of objects by doing an ANALYZE;
• Change some initialization parameters (for example: hash_join_enabled, sort_area_size, db_file_multiblock_read_count).