Statistics on indeces used
Hi, not long ago I inherited a complex application. When checking the tables I found some were using a bunch of different indeces. Is there a way to find out, which of them are really being used by the database server? It would be nice to have a statistics collected over the day to check if I can delete the unused index entries. (Now it's the unix port: Sun Ultra 60, Solaris 2.6, IDS 7.30-TC9-1) TIA Axel
If your indexes are detached, sysptprof will contain information on your
indexes. A query like below should give you meaningful results, cumulating
information for fragmented indexes.
select dbsname, tabname, sum(<whatever>)...
from sysmaster:sysptprof a, <your_db>:sysfragments b
where a.partnum = b.partn
and b.fragtype = 'I'
group by 1,2;
There is a bit of a problem here, though, in interpreting the results,
primarily because inserts and deletes to the underlying table will cause
reads on the index (in addition to iswrites or isdeletes) . Updates to the
underlying table will do the same if a column being updated figures in the
index. All the same, it isn't impossible to reach some conclusions.
If your indexes are not detached, I can't help, though I'd be interested in
learning about any method you discover.
Rudy
Axel Sander wrote:
> Hi,
>
> not long ago I inherited a complex application. When checking the tables I
> found some were using a bunch of different indeces.
> Is there a way to find out, which of them are really being used by the
> database server? It would be nice to have a statistics collected over the
> day to check if I can delete the unused index entries.
> (Now it's the unix port: Sun Ultra 60, Solaris 2.6, IDS 7.30-TC9-1)
>
> TIA
>
> Axel