Reactive Index Design - Relational Database Index Design and the Optimizers - page 104

Chapter 7

Reactive Index Design

ž Role of the EXPLAIN function and how it may be used during the index
design process to describe the selected access paths
ž How to use LRT-level exception monitoring to identify transaction SQL
problems by the use of spike reports that identify individual problems
ž Use of bubble charts to identify and analyze culprits and victims
ž Distinguishing between promising and unpromising culprits to focus on
the major potential benefits to be obtained—the tuning potential
ž How to use Call-level exception monitoring and a comparison with
LRT-level
ž Finding slow SQL calls
ž DBMS-specific monitoring issues

INTRODUCTION

We have so far focused on relatively straightforward SQL statements. Before
we address the more complex activities such as Joins, Subqueries, Unions, Star
Joins, and Multiple Index Access, it will be useful to discuss how our SQL can
be monitored and to consider the index design considerations from a reactive
point of view.
Amy Anderson and Michael Cain (6) describe the reactive query approach
vividly (page 26):

The reactive approach is very similar to the Wright Brothers' initial airplane
flight experiences. Basically the query is put together, pushed off a cliff, and
watched to see if it flies. In other words, build a prototype of the proposed
application without any indexes and start running some queries. Or, build an
initial set of indexes and start running the application to see what is used and
what not. Even with a smaller database, the slow running queries will become
obvious very quickly.
The reactive tuning method is also used when trying to understand and tune
an existing application that is not performing up to expectations.