| 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.
|