Figure 5-21 is a simple instance-level ORTA report, yet it has significant differences that highlight ASH's power. First, notice the initial parameter of 15. This parameter sets how far back in time we want to start reporting. The reporting interval is set to start 15 minutes into the past and end at the present. In addition to the wait event details, ASH's session_state indicates whether the session was waiting for a non-idle event to complete or consuming CPU. This simple column enables us to determine the percentage of time waiting (queue time) and consuming CPU (service time). And because the wait event names are also stored, we can classify the queue time as we wish. As you know, I prefer simple classifications, and those can be seen in Figure 5-21.

Figure 5-21. Very similar to any Oracle response-time report, except this report is based on ASH's sampled data starting 15 minutes ago until the time of the report run. Overall response time can be significantly decreased by focusing first on IO activity and then on CPU consumption.

Figure 5-21 shows that considering all ASH rows over the past 15 minutes with a session_state of either WAITING or ON CPU, 37% of the rows have a session_state of ON CPU and 63% of the rows have a session_state of WAITING. Said another way, over the past 15 minutes, 37% of background and server process time was spent consuming CPU and 63% of their time was spent queuing for some resource. Because every ASH row with a session_state of WAITING has the currently waiting event name in its event column, we can easily determine that of the 63% queue time, 95% was spent waiting for an IO call to complete. I could have easily further classified the IO time, but I think you get the point. We need to determine the SQL related to CPU consumption (37% of all Oracle sessions' response time) and also the SQL related to IO calls. We can use ASH to determine them both!