Continuous Physical Database Design - Automated Physical Database Design and Tuning - page 179

be necessary and the significant load on the server due to the tuning session is
wasted. Worse still, the only way to determine whether a tuning session would
be worthwhile is to actually run it! A second problem with this approach is
that the tuning tool does not take into account the resources needed to imple-
ment a new configuration. While this is not a problem in the traditional phys-
ical design problem, where a configuration is obtained and deployed only once
it might be very inadequate to spend considerable resources in deploying a con-
figuration that only marginally improves performance of subsequent queries.
An alternative approach that can address problem 1 is to determine whether
the current configuration is suboptimal a priori (i.e., before running an expen-
sive tuning tool). We next describe a technique, which we henceforth call
alerter , that analyzes a workload and quickly determines whether a tuning
session would result in a configuration significantly better than the current
one. It has the following characteristics:

• Low-overhead diagnostics: The alerter can be called repeatedly when-

ever the DBA suspects that changes might be necessary or at fixed time
intervals. The alerter works only with the information that was gath-
ered when the workload was originally optimized and does not rely on
additional optimizer calls.

• Reliable lower-bound improvement: When the alerter reports that cer-

tain improvement is possible, we can be certain that the improvement
achieved by a comprehensive tuning tool would indeed be at least as
large. This is crucial since false positives would defeat the purpose of
the alerting mechanism and are therefore unacceptable.

Figure 10.1 illustrates how the alerter fits into a physical database design
solution. As new queries are optimized and executed, the DBMS maintains
information about the workload that would later be consumed by the alerter.
After a triggering condition happens (e.g., a fixed amount of time), the alerter
is automatically launched, and it quickly diagnoses the current situation. After
the lightweight diagnostics, if the alerter determines that running a compre-
hensive tuning tool would result in an improvement beyond a certain prespec-
ified threshold, the DBA is alerted to run such a comprehensive alternative.

Improvement
hreshold

Queries Results Alert! Run tool for at

least x% improvement

Triggering mechanism
for diagnostics

Physical Design
Tuning Tool

Workload
Information

Recommendation

DBMS Alerter

Monitoring
FIGURE 10.1 Architecture for a physical design alerter. (Used with per-
mission from Bruno, N. & Chaudhuri, S. In Proceedings of the International
Conference on Very Large Databases [VLDB], 2006.)

Diagnostics Tuning