When is cost based optimization triggered? (for DBA)

Question

Helloo
When is cost based optimization triggered? (for DBA)
please reply me. i am waiting for your reply.
Thank you.

Answer 1

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:
1.    Change statistics of objects by doing an ANALYZE;
2.    Change some initialization parameters (for example: hash_join_enabled, sort_area_size, db_file_multiblock_read_count).