Databases Reference
In-Depth Information
reflect not only the depth and breadth of the index tree, but also the uniqueness of the
values in the tree, which can affect the ease with which values can be selected using the
index.
The accuracy of the cost-based optimizer depends on the accuracy of
the statistics it uses, so updating statistics has always been a must.
Formerly, you would have used the SQL statement ANALYZE to com‐
pute or estimate these statistics. When managing an older release,
many database administrators also used a built-in PL/SQL package,
DBMS_STATS, which contains a number of procedures that helped
automate the process of collecting statistics.
Stale statistics can lead to database performance problems, which is
why database statistics gathering has been automated by Oracle. This
statistics gathering can be quite granular. For example, as of Oracle
Database 10
g
, you can enable automatic statistics collection for a table,
which can be based on whether a table is either stale (which means that
more than 10 percent of the objects in the table have changed) or
empty.
The optimizer in Oracle Database 12
c
now automatically decides
whether available statistics can generate a good execution plan dur‐
ing the compilation of SQL statements. If statistics are missing or out
of date, dynamic sampling of tables automatically occurs to generate
new statistics. Also in this database release, statistics are automatical‐
ly created as part of bulk loading operations.
The use of statistics makes it possible for the cost-based optimizer to make a much more
well-informed choice of the optimal execution plan. For instance, the optimizer could
be trying to decide between two indexes to use in an execution plan that involves a
selection based on a value in either index. The rule-based optimizer might very well rate
both indexes equally and resort to the order in which they appear in the WHERE clause
to choose an execution plan. The cost-based optimizer, however, knows that one index
contains 1,000 entries while the other contains 10,000 entries. It even knows that the
index that contains 1,000 values contains only 20 unique values, while the index that
contains 10,000 values has 5,000 unique values. The selectivity offered by the larger
index is much greater, so that index will be assigned a better optimization score and
used for the query.
Testing the Effect of New Statistics
There may be times when you don't want to update your statistics, such as when the
distribution of data in your database has reached a steady state or when your queries
are already performing optimally (or at least deliver adequate, consistent performance).
