Figure 9-23. Shown is the essential application SQL information entered (obviously copy and pasted) into a template. All the information was gathered during the 30-minute collection interval from v$sql and represents only what was processed during the collection interval. Notice the most resource-consuming statement is not the slowest and consumes no more resources per execution than other statements. It's the combination of execution rate and per-execution resource consumption that makes it stand out.

The Oracle analysis has directed us to the most important application SQL, which is SQL needing blocks that do not currently reside in Oracle's buffer cache. By focusing on SQL with the highest physical IO consumption, we can significantly reduce the application impact. There is no guessing or gut feeling about this. It is a fact. However, we expect the Oracle-focused solution of increasing the buffer cache to have a profound impact, and the change requires only a single parameter adjustment and an instance cycle. We will want to reanalyze the situation during the second analysis cycle. So at this point in the analysis, we will wait before suggesting any application changes.

Figure 9-24 shows common workload metrics we will combine with our Oracle and operating system analysis when building our response-time graphs and anticipating change. Figure 9-24 also provides two distinct informational aspects: the workload metrics in both seconds and milliseconds, and response-time-classified details. It provides these details by calculating the appropriate resource consumed (for example, CPU consumption) divided by the workload metric activity during the reporting interval. For example, each logical block processed consumed 0.01507 ms-that is, 0.01507 ms/lio. This is the logical IO service time and can be useful when constructing a response-time curve based on logical IO activity.