New Challenges in Physical Database Design - Automated Physical Database Design and Tuning - page 223

at the same time (e.g., by a merge-based join operator), interference among
I/O requests would prevent the underlying disk from exploiting sequentiality,
increasing overall I/O times due to additional random seeks.
Some of the challenges involved in automatic layout recommendation in-
clude defining cost models that appropriately approximate the performance
of alternative data layouts, incorporating manageability and availability re-
quirements, reflecting the different characteristics of the underlying storage
subsystems, and reacting to interference, not only from a single query but
also due to concurrent execution of workloads.
12.3 Interactive Physical Design Tuning
Previous chapters discussed various extensions to the traditional physical
database design problem, regarding both the kinds of physical structures to
consider (Chapters 8 and 9) and generalizations to the optimizing function
and constraints (Chapters 10 and 11). These extensions result in significant
flexibility and provide additional control to database administrators that rou-
tinely tune physical database designs. At the same time, current physical
design tools are monolithic, expose tuning options that are set at the begin-
ning, and generate a final configuration to deploy into a production system
without much further user feedback. These architectural decisions in physical
design tools force users to specify the whole problem up front and prevent
even experienced DBAs from making changes a posteriori or in general inter-
acting with the system. We believe that a paradigm shift might be required
to take physical database design to the next level. Specifically, physical design
sessions should be highly interactive and allow DBAs to quickly experiment
and validate design choices. We next discuss a possible architecture that might
be more suitable for such interactive sessions.
12.3.1 An Architecture for Interactive Physical
Design Tuning
Figure 12.3 shows a layered architecture for interactive physical design tools
that can result in better and richer interaction with DBAs. While the two
lowest layers are already implemented in commercial systems, the remaining
ones differ considerably from current implementations. We next describe this
architecture in more detail.
Core DBMS: The lowest layer resides within the database system and pro-
vides native support for operations such as what-if optimization (see
Chapter 5) and access path request interception (see Chapter 4).