Figure 9-33. Shown is the operating system analysis information. Because the Oracle load is almost entirely CPU-based, targeting heavy logical IO SQL statements by creating the status column index reduced CPU utilization to 7%. Oracle is submitting virtually no IO requests to the operating system.

The application situation has profoundly changed. Oracle is now processing fewer logical IOs while at the same time executing more SQL statements. This means users are getting more work done but consuming fewer resources! The addition of the status column index had a much larger and positive impact than we anticipated. Clearly, there were other SQL statements that benefited from the index creation. Over the 30-minute interval, we anticipated Oracle CPU consumption would decrease from 1,257 seconds down to 1,027 seconds, but in reality, the consumption decreased to a staggering 344 seconds.

Comparing the top SQL statements in Figure 9-29 (before index addition) with Figure 9-34, notice there is now a new top logical IO-consuming SQL statement along with the previous number three statement (SQL_ID ending in ggt). If performance is to be further improved, we have once again clearly identified (and supported by an OraPub 3-circle ORTA-focused approach) the next two SQL statements to address.