System Statistics

Abstract

The query optimizer used to base its cost estimations on the number of physical reads needed to execute SQL statements. That method is known as the I/O cost model. The main drawback of this method is that single-block reads and multiblock reads are equally costly. Consequently, multiblock read operations, such as full table scans, are artificially favored. Before system statistics were introduced, especially in OLTP systems, the optimizer_index_caching and optimizer_index_cost_adj initialization parameters were used to work around this problem (see Chapter 9 for coverage of both parameters). In fact, the default values used to be appropriate for reporting systems and data warehouses only. Currently, a new costing method, known as the CPU cost model, is used to address this flaw. To use the CPU cost model, additional information about the performance of the system where the database engine runs, called system statistics, has to be provided to the query optimizer. Essentially, system statistics supply the following information: