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.

ASH can be used in other ways also. Because it contains the client identifier as well as the session identifier and serial number, we can create an entire group of similar reports. Just as we drilled down from our initial instance-level response-time analysis in Figure 5-21 into both CPU consumption and wait time, we could have also drilled down to the session or to the sessions tagged with a specific client identifier. In fact, we could have easily performed an ORTA based on a specific client identifier. The possibilities are endless and powerful. I hope you have gained an appreciation for how ASH can be used during performance firefighting.