Database Reference
In-Depth Information
Figure 12-4.
Session output on the addition of a large number of rows
The session output includes multiple
auto_stats
events since the threshold was exceeded by the large-scale
update this time. You can tell what each of the events is doing by looking at the details. Figure
12-4
shows the
job_
type
value, in this case
StatsUpdate
. You'll also see the statistics that are being updated listed in the
statistics_list
column. Another point of interest is the Status column, which can tell you more about what part of the statistics
update process is occurring, in this case “Loading and update stats.” These SQL activities consume some extra CPU
cycles. However, by doing this, the optimizer determines a better data-processing strategy and keeps the overall cost
of the query low. After the statistics update completes, the query then runs using the up-to-date statistics to arrive at
the execution plan shown in Figure
12-3
.
Drawbacks of Outdated Statistics
As explained in the preceding section, the auto update statistics feature allows the optimizer to decide on an
efficient processing strategy for a query as the data changes. If the statistics become outdated, however, then the
processing strategies decided on by the optimizer may not be applicable for the current data set and thereby will
degrade performance.