When adding the status column index, we anticipated only a 3% decrease in CPU utilization, but in reality, there was a 13% drop! Always try to be conservative, but in this case, the anticipated performance impact was simply wrong. We got lucky because many other SQL statements were impacted (for the better) in addition to the four we targeted. Because we did not analyze all possible affected SQL statements, there could have easily been other statements negatively impacted, eliminating any performance gain achieved from our targeted efforts.

I could have simply left the index addition section out of the book, but I included it for two reasons. First, to provide another example of how performance change can be anticipated. Second, so you can observe how easy it is to be wrong by not thinking through a change.

Each cycle of this performance analysis used data from a 30-minute sample. While different sample durations could have been used, by using the same sample interval, direct numeric comparisons without a unit of time are possible. For example, I mentioned when the status column index was added, the number of SQL executions increased from 48.7K to 53.1K. If the first sample interval was 30 minutes yet the second interval was 60 minutes, we could not have responsibly made this direct comparison. Instead of stating there was a total of 53.1K statement executions, we could have stated the statement execution rate was 29.5 exec/sec.