As shown in Figure 9-29, the statement with a SQL_ID ending in d6w consumed nearly 22.7 million logical IOs during the 30-minute reporting period, which is about 42.3 thousand logical IOs during each of its 536 executions. By tracing the d6w SQL statement, it was confirmed that a typical execution touches around 42.3 thousand logical buffers. It was also obvious that the large customer table was being full-table scanned! By simply creating an index on the status column and rerunning the query, only three logical buffers were touched. (While indexing a status column usually will not produce an improvement like this, in this application, it was indeed the case.) This means even if the statement is run 536 times, only 1,608 buffers will be touched. And since each logical IO take around 0.009 ms, during the 30-minute interval, the statement should consume only 14.5 seconds of CPU (1,608 _ 0.009).

But the impact is more far-reaching, because creating an index on the status column also impacts three other statements out of the top five logical IO statements. The other three statements also touch only three logical IOs per execution. Of the five top logical IO statements, only the statement with the SQL_ID ending in ggt is not improved by the new index. As you'll see later, the lack of a thorough index impact analysis will have unintended consequences.

Table 9-3 details one way to calculate the CPU consumption change for multiple SQL statements. By creating the status column index, each of the queries will consume only three logical IOs per execution. Based on their number of executions during the 30-minute sample interval, the expected logical IOs are calculated. Since each logical IO consumes around 0.0093 ms, the expected total CPU consumption per tuned SQL statement is calculated. When combined, the tuned statements will now consume only 0.0231 second of CPU, compared to the initial 230.553 seconds.