Databases Reference
In-Depth Information
Table 12.6
Quality of DTA vs. Hand-tuned Design on Internal Customer Workloads
Improvement of
hand-tuned design
over default
Improvement of
DTA design over
default
Tuning time
(hr:min)
Wo rk lo a d
CUST1
82%
87%
0:35
CUST2
6%
41%
8:21
CUST3
-5%
0%
15:14
CUST4
0%
50%
1:07
results. Chaudhuri et al. studied two different workloads: an industry standard work-
load and a synthetic workload. They found the compression strategy was effective in
removing a large percentage of the statements in the input workload, between 60%
and 80% reduction, while the quality loss in the recommendations was less than 18%.
They also found that the processing time of the advisor reduced in approximately the
same proportion as the reduction in workload size.
Figure 12.21
Impact of workload compression.
Outside of the published literature there have been scores of results reported by
users of these technologies (from all vendors) attesting to the value these features have
provided for production databases. Huge benefits have been reported often improving
performance by 70% or more, while dramatically reducing CPU consumption and
other system resources.
