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.

Figure 5-23 shows the same ASH response time report shown in Figure 5-21, with one very important difference. This time, we want the response time breakdown for a specific SQL statement, not all the SQL statements. The second parameter is the SQL statement identifier. If we enter a %, then all statements will be considered; otherwise, only the specific SQL identifier we enter will be considered.