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.

As we did with the top physical IO SQL statement (Figure 5-23), we also want to understand the top CPU-consuming SQL statement's response-time profile. Figure 5-25 is again based on the response time report, ashrt.sql. But just as in Figure 5-23, we want the response-time profile for a specific SQL statement. The result, shown in Figure 5-25, indicates the top CPU-consuming statement when running spends 73% of the time consuming SQL! We can reduce overall SQL statement CPU consumption by either tuning this specific statement or by reducing its execution rate. Altering the execution rate is known as workload balancing, and while not a technically exciting solution, it can have a massive impact on CPU consumption during peak activity and business times.