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.

A subtle yet very distinct analysis step occurred. Without ASH,11 when performing our ORTA, the wait event db file scattered read directs us to look for SQL requesting blocks outside Oracle's cache. We would typically look at the view v$sql and sort the SQL by the disk_reads column. Sure enough, we would discover a few SQL statements responsible for most of the scattered reads. But the situation is entirely different with ASH. Because v$activity_session_history session samples contain both the SQL identifier (column SQL_ID) along with the wait event (column EVENT), we absolutely know the SQL associated with the wait event. So we don't infer the associated SQL, which does leave a little room for mistakes.12 As long as ASH samples frequently enough, the chance of identifying the wrong SQL statement is about zero.