Figure 5-23 shows the same ASH response time report shown in Figure 5-21, with one very important difference. This time, we want the response time breakdown for a specific SQL statement, not all the SQL statements. The second parameter is the SQL statement identifier. If we enter a %, then all statements will be considered; otherwise, only the specific SQL identifier we enter will be considered.

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.