Read an Excerpt
Chapter 9: Table Joins and Other Advanced Query Tuning
Tuning Using Simple Mathematical TechniquesThis section discusses some simple but effective mathematical techniques you can use to significantly improve the performance of some Oracle SQL-based systems. These techniques can leverage the effectiveness of Oracle performance diagnostic tools and uncover hidden performance problems that can be overlooked by other methods. It also makes it easier to make performance predictions at higher loads. This section was provided by Joe A. Holmes. I am extremely grateful for his contribution, as I believe it ties all the chapters of this book together.
The methodology called Simple Mathematical Techniques involves isolating and testing the SQL process in question under ideal conditions, graphing the results of rows processed versus time, deriving equations using simple methods (without regression), predicting performance, and interpreting and applying performance patterns directly to tuning SQL code.
Traditional Mathematical Analysis
First of all, do not be intimidated by this section. You will be able to understand this, and with this information provided, it will help you to predict response times for your queries as the size of the tables grow.
Traditional mathematical methods are very useful for analyzing performance. These may include graphing performance metrics on an x-y coordinate axis to obtain a picture of what a process is really doing and applying Least Squares Regression or Polynomial Interpolation to derive equations for predicting performance at higher loads. Computer science academics and specialists make extensive use of these techniques for performance analysis. There are problems with using mathematical techniques for performance analysis. First, textbook notation and explanations are often very complex and difficult to understand. The majority of math textbooks I have encountered regarding approximation and interpolation, for example, are steeped in theory rather than providing clear and practical examples.
Second, there is little or no information on how to apply this kind of analysis directly to tuning SQL code. This is likely because SQL analysis requires more specific interpretations to be useful rather than something more broad or general.
Seven Steps of the Methodology
The following are seven steps in the methodology. Note that Deriving Performance Equations and Pattern Interpretation are discussed in more detail.
1. Isolating the SQL Code in Question
The SQL code in question is isolated from surrounding system code and placed in a SQL*Plus or PL/SQL script that can be run independently to duplicate the production process.
2. Running Tests under Ideal Conditions
In this context, "ideal" is defined as one SQL process running on a dedicated machine with hardware processing power fixed and executed under high-volume data.
3. Graphing Performance Observations on an x-y Coordinate Axis
From tests, the number of rows processed (x) versus time (y) for each SQL statement within a process is graphed on an x-y coordinate axis. We refer to this as a row-time metric. Under ideal conditions, the optimizer is for the most part more mechanical and less random, creating a more clearly defined and predictable trend line. The basic line shape can provide clues to the cause of underlying performance problems.
4. Using Simple Equation Determination
Once points are plotted on a graph, we assume that what appears straight is a linear function and what appears curved upwards is a quadratic function. (Other shapes may appear, but are treated as beyond the scope of this section.) From these observations, we can use either a simple two-point linear or three-point quadratic method to determine the equations. Both methods can be done easily by hand or with a basic calculator. Each separate SQL statement is graphed and analyzed individually.
5. Predicting Performance
Derived equations can be used to predict performance at much higher loads than are practical to test. Because the accuracy of the predictions may decrease as the predicted load increases, it is suggested that only ballpark predictions be made.
6. Interpreting Performance Patterns and Experimentation
The shape of the performance lines and the nature of the equations can provide clues to the cause of underlying performance problems and support (or sometimes contradict) the interpretations of diagnostic tools. Experiments can be conducted on SQL code based on pattern clues and the correction applied to production code. Tests of an improved process can be graphed again and the results compared with the original process.
7. Keeping a Record of Results to Build Expertise
It is important to keep a record of before and after performance graphs, what the true cause of performance problems were and what effective solutions were found in order to build up your expertise at using both these mathematical methods and your interpretation of Oracle diagnostic tools. Graphs provide hard evidence of performance problems that can be presented in a clear visual form to management and end users.
Deriving Performance Equations
The following discusses two simple methods for equation determination based on simplified versions of Newton's Divided Difference Interpolating Polynomial [1]. These methods can be used if we assume what appears as a straight line is linear and what appears as upward sloping is quadratic....