Database Reference
In-Depth Information
Chapter 7
System Statistics
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.
1
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:
•
Performance of the disk I/O subsystem
•
Performance of the CPU
Despite its name, the CPU cost model takes into consideration the cost of physical reads as well. But instead
of basing the I/O costs on the number of physical reads only, the performance of the disk I/O subsystem is also
considered. Don't let the name mislead you.
A default set of system statistics is always available. As a result, by default, the CPU cost model is used. Actually,
the only way to use the I/O cost model is to specify the
no_cpu_costing
hint at the SQL statement level or by setting
an undocumented initialization parameter. In all other cases, the query optimizer uses the CPU cost model.
The dbms_stats Package
The
dbms_stats
package provides a comprehensive set of procedures to manage system statistics. By default, the
package modifies the data dictionary. However, it's possible with most of the package procedures to work instead on
a user-defined table stored outside the data dictionary. Such a table is what I call the
backup table
. This table is mainly
used in two situations:
•
For gathering system statistics without having to store them in the data dictionary and, as a
result, without immediately making them available to the query optimizer
•
For moving system statistics between two databases
Moving statistics between databases is done by exporting the system statistics into a backup table on the source,
moving the backup table to another database, and importing its content into the target data dictionary. Moving system
statistics between databases is a way to make sure that all databases related to a given application (for example,
development, test and production) are using the same system statistics.
