Introduction to Optimizer Statistics

The optimizer cost model relies on statistics collected about the objects involved in a query, and the database and host where the query runs.Statistics are critical to the optimizer's ability to pick the best execution plan for a SQL statement.Optimizer statistics include the following:Table statisticsNumber of rowsNumber of blocksAverage row lengthColumn statisticsNumber of distinct values (NDV) in a columnNumber of nulls in a columnData distribution (histogram)Extended statisticsIndex statisticsNumber of leaf blocksNumber of levelsIndex clustering factorSystem statisticsI/O performance and utilizationCPU performance and utilizationAs shown in Figure 10-1, the database stores optimizer statistics for tables, columns, indexes, and the system in the data dictionary. You can access these statistics using data dictionary views.