From:Steve Adams
Date:31-Oct-2001 14:19
Subject:   Breakdown of I/O by table

The following query may help. In my experience it does a fairly good job of identifying the tables responsible for most of the I/O. It could be enhanced so that the I/O for statements with multiple base tables would be attributed to those tables in proportion with their size, but that would impact the performance of the query quite significantly and I have been satisfied with this approximation for my purposes to date.

    select /*+ ordered use_hash(d) use_hash(c) */
      o.kglnaown  owner_name,
      o.kglnaobj  table_name,
      sum(c.kglobt13)  possible_disk_reads
    from
      sys.x_$kglob  o,
      sys.x_$kgldp  d,
      sys.x_$kglcursor  c
    where
      c.inst_id = userenv('Instance') and
      d.inst_id = userenv('Instance') and
      o.inst_id = userenv('Instance') and
      o.kglhdnsp = 1 and
      o.kglobtyp = 2 and
      d.kglrfhdl = o.kglhdadr and
      c.kglhdadr = d.kglhdadr
    group by
      o.kglnaown,
      o.kglnaobj
    order by
      sum(c.kglobt13) desc;

I understand how to display the amount of I/O the is being done to a tablespace and to a datafile, but is there a way to display the amount of I/O to a table?

The reason that I ask this is I have a tablespace that has a number of tables of various sizes on it. Examining the statistics indicates that I/O to this tablespace is 10-15 times greater than any of my other tablespaces. If I can't display I/O per table, my only other recourse is to make a best guess and move a table to another tablespace and run for a while. That is unless you have a less painful solution.