Databases Reference
In-Depth Information
Figure 12.14
SQL Access Advisor selection options.
shown in Figure 12.15 shows the expected savings in I/O and in projected total work-
load execution time. Like DB2's Design Advisor, Oracle's advisor has the ability to
determine what indexes and materialized views are not used by the supplied workload.
This can be useful in detecting obsolete objects. Within the Oracle SQL Access Advisor
this is called the "evaluation mode.”
The Recommendations page, shown in Figure 12.16, lists the recommendations
reported by the advisor along with estimates on the relative benefit of each. This allows
the user to select only the more valuable recommendations if either the storage require-
ment is high, or the benefit to some of the recommendations seems marginal.
12.3
Data Sampling for Improved Statistics
during Analysis
Although databases do keep statistics on the database they store, the statistics are not
comprehensive and they do get out of date. They can't be comprehensive; the combina-
tions, permutations, and variants of possible statistics that can potentially be kept are
unfathomably large. As a result databases usually keep a reasonably complete set of sta-
tistics on each column of every table and each key of every index. Increasingly, the col-
lection of statistics is an automated task, which reduces the need for manual administra-
tive commands and reduces the chances of the statistics becoming stale over time. How-
