Capture I/O for individual table and indexes Oracle Tips by Burleson Consulting March 16, 2004 - Updated March 22,2007

Have you ever needed to know the write I/O for a specific Oracle table?  Prior to Oracle9ir2 you would have to segregate the table into a separate tablespace to see segment-level I/O statistics.

In Oracle9i release 2 we see an important enhancement to Oracle STATSPACK, most notably:

1 - Track reads writes for specific segments
2 - Track buffer busy waits by table or index
3 - Collect historical SQL execution plans using the level 8 snapshot

Using the level 7 STATSPACK collection it is now possible to track I/O at the individual segment level, showing disk I/O for any Oracle table or index.

SQL> execute statspack.snap (i_snap_level=>7, i_modify_parameter=>'true');

A level 7 STATSPACK snapshot collects all segment-level statistics, including logical and physical reads, row lock, and buffer busy waits. 

Details on I/O-based tuning are in my book Oracle Tuning: The Definitive Reference.

The ability to track buffer busy waits at the table and index level is especially important for removing segment header contention.  Here is a great article on buffer busy wait tuning. 

Also, see these notes on monitoring Oracle table and index I/O:

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.