Figure 9-32 shows a rather dramatic decrease in CPU consumption over the 30-minute sample interval. When adding the status column index, we expected the total Oracle CPU consumption to drop to around 1,027 seconds. However, it dropped to 343 seconds! So, obviously the index had a much broader (and positive) impact than we anticipated. Based on the ORTA, further performance improvements should once again focus on reducing CPU consumption.

Figure 9-32. Shown is the 30-minute interval ORTA as a result of an increase in the buffer cache and adding the status column index. Compared to Figure 9-27, as expected, total queue time is about the same and is insignificant. Far surpassing our expectations, total service time decreased from 1,254 to 344 seconds! Clearly, the status column index touched far more SQL statements than our top SQL report showed.

Figure 9-33 shows the operating system is looking even better than before! The CPU utilization dropped from 20% to 7%, and the IO subsystem is receiving virtually no IO requests from Oracle. The status index creation reduced Oracle CPU consumption far more than our anticipated 3%. The index impact was so prolific that it resulted in a 13% CPU consumption reduction. As with the prior tuning cycle, the only way to decrease CPU-related response time is to either use faster CPUs or reduce SQL statement logical IO consumption through tuning or workload balancing.