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.

While the improvement seems dramatic, only when users trigger multiple and serial executing SQL statements are they likely to feel any difference. Additionally, total sample interval Oracle CPU consumption is 1,257 seconds (Figure 9-27), so a decrease of around 230 seconds may not result in much of a utilization improvement. But let's do the math, create the index, and see what happens.