DEVFYI - Developer Resource - FYI

When is cost based optimization triggered? (for DBA

ORACLE Interview Questions and Answers (Part 3)

(Continued from previous question...)

381. When is cost based optimization triggered? (for DBA

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).

(Continued on next question...)

Other Interview Questions