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.
 
Search WWH ::




Custom Search