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.

Figure 9-34. Shown is the essential application SQL information. Because of the index addition, the targeted high logical IO SQL statements no longer appear on the top SQL report! In addition, the top logical IO SQL statements now consume 8.4M and 4.1M logical IOs, compared to the earlier case where the top two statements consumed 22.6M and 10.3M logical IOs, respectively (Figure 9-29). The status column index has had a profound impact on the most resource-consuming SQL.

It is also very encouraging that as result of the additional index, the top two logical IO statements consumed a combined 12.5M logical IOs (Figure 9-34), whereas before the index addition, the top two consumed 32.9M logical IOs (Figure 9-29). So, by aligning our ORTA with the application analysis, we correctly targeted the high-impact SQL statements.