Databases Reference
In-Depth Information
Figure 12.18 Options for executing automated design utilities with both test and production
servers available.
customer sites can be reproduced by the vendor on local machines to help analyze
reported problems. An important invariant to maintain is that the recommendation
that is produced when tuning on the test server is identical to the recommendation that
would be produced had the tuning been done directly against the production server. If
this invariant is not maintained arbitrary degradation in quality may result. This
requires that all statistics that would have been used on the production server be avail-
able on the test server as well. Products like DB2 and SQL Server provide services to
easily enforce that statistics on the test system match those on the production system.
12.6
Experimental Results from Published Literature
Zilio et al. [2004] tested automated physical design technology against an industry-
standard decision support workload (Table 12.3). They compared their advisor against a
baseline TPC-H database that had a “reasonable” but not optimal design (Figure
12.19). The baseline database had all the major indexes defined and a reasonable parti-
tioning scheme. The database was a relatively small 1 GB TPC-H database stored on an
8 CPU AIX 5.2 system with four logical partitions. The workload contained all the 22
TPC-H queries. In these experiments the Design Advisor was able to finish the design
of all features in about 10 minutes. Most significantly the resulting design improved the
performance of the TPC-H workload by 6.5 times (85% reduction in execution time).
