Oracle Data Structures - Oracle Essentials - page 125

Oracle8 and later versions, by default, use the cost-based optimizer to identify the op‐
timal execution plan. And, since Oracle Database 10 g , the cost-based optimizer is the
only supported optimizer. To properly evaluate the cost of any particular execution plan,
the cost-based optimizer uses statistics about the composition of the relevant data
structures. These statistics are automatically gathered by default since the Oracle Da‐
tabase 10 g release. Among the statistics gathered in the AWR are database segment
access and usage statistics, time model statistics, system and session statistics, statistics
about which SQL statements that produce the greatest loads, and Active Session History
(ASH) statistics.
How statistics are used
The cost-based optimizer finds the optimal execution plan by assigning an optimization
score for each of the potential execution plans using its own internal rules and logic
along with statistics that reflect the state of the data structures in the database. These
statistics relate to the tables, columns, and indexes involved in the execution plan. The
statistics for each type of data structure are listed in Table 4-1 .
Table 4-1. Database statistics

Data structure Type of statistics

Table Number of rows

Number of blocks
Number of unused blocks
Average available free space per block
Number of chained rows
Average row length

Column Number of distinct values per column

Second-lowest column value
Second-highest column value
Column density factor

Index Depth of index B*-tree structure

Number of leaf blocks
Number of distinct values
Average number of leaf blocks per key
Average number of data blocks per key
Clustering factor

Oracle Database 10 g and more current database releases also collect overall system sta‐
tistics, including I/O and CPU performance and utilization. These statistics are stored
in the data dictionary, described in this chapter's final section, “Data Dictionary
Tables” on page 134 .

You can see that these statistics can be used individually and in combination to deter‐
mine the overall cost of the I/O required by an execution plan. The statistics reflect both
the size of a table and the amount of unused space within the blocks; this space can, in
turn, affect how many I/O operations are needed to retrieve rows. The index statistics