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.

Figure 5-22. Each ASH sample is related to a single SQL statement and if waiting, there will be an associated wait event. In this situation, we want to list the SQL that has been waiting the most for multiblock reads over the last 15 minutes.