Donald K. Burleson Oracle Tips

Tracking I/O Waits on Specific Tables and Indexes

It should be clear that the DBA still must be able to translate the file number and block number into a specific table or index name. This can be accomplished by using the dba_extents view to determine the start block and end block for every extent in every table. Using dba_extents to identify the object and its data block boundaries, it becomes a trivial matter to read through the new table and identify those specific objects experiencing read waits or buffer busy waits. The next step is to add the segment name by joining into the dba_extents view.

The following is the output from this script. Here, one can see all of the segments that have experienced more than 10 disk read wait events:

Wait       Segment                           Segment            Wait

Event      Name                              Type              Count

---------- --------------------------------- ---------- ------------

SEQ_READ   SYSPRD.S_EVT_ACT_F51              INDEX                72

SEQ_READ   SYSPRD.S_ACCNT_POSTN_M1           INDEX                41

SEQ_READ   SYSPRD.S_ASSET_M3                 INDEX                24

SEQ_READ   SYSPRD.S_ASSET_M51                INDEX                19

SEQ_READ   SYSPRD.S_COMM_REQ_U1              INDEX                11

This shows the exact indexes that are experiencing sequential read waits, and now there is an important clue for SQL tuning or object redistribution strategy.

The next step is to identify all hot blocks to complete the analysis.  This can be accomplished by examining the dba_hist_waitstat table for any data blocks that have experienced multiple waits.  In this sample output, each segment, the exact block where the wait occurred, and the number of wait events can be seen:

                                                            Multiple

                                                               Block

Wait       Segment                    Segment         Block     Wait

Event      Name                       Type           Number    Count

---------- -------------------------- ---------- ---------- --------

SEQ_READ   SYSPRD.S_EVT_ACT_F51       INDEX         205,680        7

SEQ_READ   SYSPRD.S_EVT_ACT           TABLE         401,481        5

SEQ_READ   SYSPRD.S_EVT_ACT_F51       INDEX         471,767        5

SEQ_READ   SYSPRD.S_EVT_ACT           TABLE           3,056        4

SEQ_READ   SYSPRD.S_EVT_ACT_F51       INDEX         496,315        4

SEQ_READ   SYSPRD.S_DOC_ORDER_U1      INDEX          35,337        3

Since it identifies those data blocks that have experienced multiple block waits, this report is critical. It is then possible to go to each data block and see the contention on a segment header.

---

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

Copyright © 1996 -  2011 by Burleson Enterprises. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.