how can i see the I/O statistics per table basis? 1

[beksalur]

how can i see the I/O statistics per table basis?In my database, it guess some tables spoil the balance of i/o. But i want to be sure of these tables.there is a utility in Unix called iostat for disk basis,but i want to see the disk I/O statistics table by table.

 

[vpshriyan]

Hi,

You may try the following SQL:

select
substr(dbsname,1,18) dbname,
substr(tabname,1,18) table,
lpad(sum(pagreads),8,' ') pagesread,
lpad(sum(pagwrites),8,' ') pageswrote,
lpad(sum(seqscans),8,' ') seqscans
from sysmaster:sysptprof
where tabname not matches 'sys*' and
(isreads > 0 or iswrites > 0)
group by dbsname, tabname
having (sum(pagreads) > 0 or
sum(pagwrites) > 0 or
sum(seqscans) > 0)

Regards,
Shriyan

 

[calyanram]

TRY THIS SQL:


SELECT stn.dbsname,
stn.tabname,
DBINFO("dbspace", sti.ti_partnum) dbspace,
sti.ti_nptotal *
(
select sh_pagesize from sysshmvals
)/1024/1024 mb_total,
(pf_dskreads + pf_dskwrites) disk_io

FROM systabnames stn, systabinfo sti, sysptntab spt

WHERE sti.ti_partnum = stn.partnum

AND sti.ti_partnum = spt.partnum
AND sti.ti_nkeys = 0
AND stn.tabname not like "TBLSpace%"
AND stn.dbsname not like "HASH%"

ORDER BY disk_io DESC;


tanks
calyan