Databases Reference
In-Depth Information
1.
Baseline:
The performance of the benchmark without MDC. Table 12.1 describes
those traditional RID (row) indexes used for the baseline experiment, which had a
cluster ratio quality of 5% or better, a measure of percentage of data that is well
clustered along one dimension.
2.
Advisor 1:
The performance of the benchmark using the topmost MDC design
of the Advisor.
3.
Advisor 2:
The performance of the benchmark using the second best MDC
design for the Advisor. This is a second best design for MDC that the advisor
discovered in its search through thousands of possible designs.
4.
Expert 1:
The MDC design used during IBM's most recent 2003 TPC-H publi-
cation. According to TPC-H guidelines, the MDC design was constrained to
clustering exclusively on base columns (coarsification was not permitted).
5.
Expert 2:
The top MDC design provided by the research and development sci-
entists who developed MDC at IBM.
6.
Expert 3:
An alternative MDC design provided by the DB2 MDC development
team.
In these experiments, all of the MDC designs showed significant benefit over the
baseline throughput. The rank ordering of the five MDC designs according to their per-
formance benefit (Figure 12.20) was Advisor 2 with 11.12%, Expert 1 with 13.35%,
Expert 3 with 14.20%, Advisor 1 with 14.54%, and Expert 2 with 18.08%. Signifi-
cantly, Advisor 1, which represents the DB2 Design Advisor's best recommendation,
was measurably superior to DB2 Design Advisor 2 and both Expert 1 and Expert 3.
Agrawal et al [2004] published experimental results with SQL Server DTA show-
ing the quality and performance of the advisor against internal users of SQL Server.
“Internal users” are users of the product within the Microsoft corporation. No state-
ment was made about the tuning skill of the internal users (i.e. they may or may not be
experts). In this experiment four database systems that were hand tuned for physical
database design by users. These hand-tuned designs were compared against designs rec-
ommended by DTA and against the default database design (which includes no
indexes or materialized views except for those required for RI and uniqueness). The
hand tuned workload performance and the DTA tuned workload performance are
both compared against the default design. Table 12.5 lists the four customer workloads
that were used.
The database sizes range from 60MB to 106GB. Similarly the size of the schema
varied dramatically across these workloads from 11 tables in the smallest case, to 4374
in the largest. The results of these tests are shown in Table 12.6. In all cases the tuning
advisor outperformed the human designers. Also worth noting is that the tuning time
ranged from roughly half an hour to over 15 hours. This illustrates that physical data-
base design, even when performed at the speed of a computer, is a very complex task.
