Oracle monitoring table I/O Oracle Tips by Burleson Consulting

If you are using approved RAID (SAME), the Stripe-and-Mirror-Everywhere architecture of Oracle 10g Automatic Storage Management (ASM), a.k.a. RAID 1+0 or RAID 10, then you may not be interested in tracking I/O for specific tables and indexes.

Also, remember that most 64-bit databases will have a large data buffer cache (or a large disk RAM cache), such that "physical reads" might be overstated.  However, locating tables that are exposed to excessive "consistent gets" might indicate bad SQL that can be located via v$sql_plan and tuned by adding indexes.

Method for tracking object-level I/O:

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');

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

We also have the  $ORACLE_HOME/rdbms/admin/catio.sql, that creates a stored procedure called sample_io and a view called io_per_object which holds real-time data on I/O per table.
 

select * from io_per_object;

For more information on Oracle tuning internals, see the book "Oracle Tuning: The Definitive Reference",

Is your RAC database Healthy? Get the experts at Burleson Consulting to conduct a two day RAC health check and ensure the health of your RAC database.  Why guess?  Have your RAC database certified by experienced RAC experts.