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!

Figure 5-22 ranks SQL statements that waited for scattered reads over the past 15 minutes. Figure 5-21 shows 95% of the wait time is IO-related and 40% of the IO waits are related to multiblock requests (see the db file scattered read entry). Figure 5-22 shows that just one SQL statement is responsible for 32% of the multiblock reads. So an application-focused solution is to tune the SQL statement 3r5xuxmggzwt8.