As we should expect, Figure 5-23 shows the top scattered read statement did indeed spend a considerable amount of its response time (50%) over the past 15 minutes waiting on multiple block requests to complete. This report confirms tuning this single statement focused on multiblock reads will significantly impact the overall system's physical IO requirements and also this particular SQL statement's response time.

Figure 5-23. This is the same ASH-based response time report as shown in Figure 5-21. The difference is in the second parameter, the SQL identifier. We now want a response time profile for a single SQL statement over the past 15 minutes. This particular SQL statement is waiting a significant amount of time for IO requests to complete.

Our ASH-based instance-level response-time analysis report shown back in Figure 5-21 indicated that over the past 15 minutes, active sessions where consuming CPU 37% of the time. So it's important we identify those CPU-consuming SQL statement(s) and tune them focusing on CPU consumption, which means logical IO. Figure 5-24 shows the top CPU-consuming SQL statements over the past 15 minutes. As I mentioned, each ASH row contains the column session_state. If the column value is ON CPU, we know the SQL statement is currently consuming SQL and not associated with a wait event. Figure 5-24 shows that, considering all the ASH rows over the past 15 minutes, the SQL statement ajgxt6x8dmsay accounted for 56% of all the ON CPU rows. This statement needs to be tuned focusing not on disk reads, block gets, or physical IO activity, but instead on buffer gets-that is, logical IO activity.