Oracle Initialization Parameters that Affect the CBO

The Oracle optimizer_mode sets an overall performance goal for the CBO in terms of throughput versus response time. In Oracle Database 10g, the default setting (all_rows), primes the CBO to select for overall best throughput—the optimizer will choose the plan that delivers all the rows in a query in the shortest possible overall time. On the other hand, the first_rows setting will cause the optimizer to provide fastest response time by choosing the plan that delivers an initial subset of rows more quickly, and the remainder of the query in a longer period of time than the all_rows setting.

The "first_rows_n" setting (new as of Oracle9i Database) lets you balance the possible variances in this continuum (throughput versus response) more directly, by letting you set the number of rows to 1, 10, 100, or 1000. In Oracle Database 10g, if the query contains a rownum predicate, the CBO automatically switches to first_rows_n mode, deriving the value of n from the rownum predicate.

Another important optimizer initialization parameter is optimizer_dynamic_sampling. Statistics on database objects (size of tables, indexes, number of rows in tables, number of distinct values in columns, percentage of table below the high-water mark, and so on) play a key role in estimating costs (see Table 2), and if the CBO can't find up-to-date statistics to work with during the cost estimation phase of statement processing, it can dynamically sample statistical data at that time—if this parameter is set. The default level in Oracle Database 10g is 2, which means the CBO will use dynamic sampling on nonanalyzed tables to collect necessary statistics from them while optimizing the query.