Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

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.

 

SEE CODE DEPOT FOR FULL SCRIPTS


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:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 


”call

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.