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.

Figure 5-24. Our response-time report shown in Figure 5-21 indicated a significant portion of the response time is related to CPU consumption. This ASH-based report shows the top CPU-consuming statements over the past 15 minutes. When a SQL statement is consuming CPU, its session_state is ON CPU.

Figure 5-25. Based on Figure 5-24, we know this particular SQL statement is the largest CPU consumer. This report shows us the time classification for this particular statement. Clearly, focusing on CPU-reducing tactics, like reducing logical IO activity, will have a significant performance-improving impact.