Database Reference
In-Depth Information
4 FROM dba_optstat_operations
5 WHERE operation = 'gather_system_stats'
6 AND start_time > to_date(:now,'YYYYMMDDHH24MISS');
------------------------------------------------------------------------------------------
| Operation | Operation | Start Time | End Time | Additional Info |
| Id | | | | |
------------------------------------------------------------------------------------------
| 4928 | gather_system_stats | 25-SEP-13 | 25-SEP-13 | Parameters: |
| | | 16.28.35.528238 | 16.28.37.105673 | [gathering_mode: |
| | | +02:00 | +02:00 | noworkload] |
| | | | | [interval: 60] |
| | | | | [statid: ] |
| | | | | [statown: ] |
| | | | | [stattab: ] |
------------------------------------------------------------------------------------------
Be aware that log information is purged by the same mechanism as the statistics history described earlier.
Both, therefore, have the same retention period.
Impact on the Query Optimizer
System statistics have a direct impact on the costs estimated by the query optimizer. Most of the statistics, when
available, are always used. Some, however, are used by the query optimizer only for estimating the costs of particular
execution plans. Specifically,
mbrc
is used only when multiblock reads are involved;
maxthr
and
slavethr
are used
only for SQL statements that are considered to be executed in parallel.
This section illustrates some usages. Others are covered in Chapter 9 in the discussion of how the query
optimizer estimates the cost of full table scans.
■
the formulas provided in this section, with a single exception, aren't published by Oracle. Several tests
show that they're able to describe how the query optimizer estimates the cost of a given operation. in any case, they
can't be claimed to be precise or correct in all situations. they're provided here to give you an idea of how system
statistics influence query optimizer estimations.
Caution
System statistics as described in this chapter are available as of 10.1 only. if you set the
optimizer_features_enable
initialization parameter to a value through 9.2.0.8, the query optimizer doesn't always behave as explained here. Because
such a configuration isn't common at all, no further information about the differences in behavior is provided. refer to
Chapter 9 for information about
optimizer_features_enable
.
When system statistics are available, the query optimizer computes two costs: I/O and CPU. Chapter 9 describes
how I/O costs are computed for the most important access paths. Very little information is available about the
computation of CPU costs. Nevertheless, we can imagine that the query optimizer associates a cost to every operation
in terms of CPU. For example, Formula 7-1 is used to compute the CPU cost of accessing a column.
