Re: unused indexes (Informix 7.31)
There was an thread not long ago:
On Mar 31, 4:38 pm, tomc...@gmail.com wrote:
>> howdy
>> does informix have system views/meta data that indicates the last time
>> an index was used?
>> thanks
>> tom
> Will need some mods for IDS 10+ if using CPS (configurable page
> sizes), and perhaps a tweak to get rid of system catalogs (unless
> wanted).
>
> HTH
> Mark Scranton
> Xtivia Inc.
>
> /* start here */
>
> database sysmaster;
> set isolation dirty read;>
> select sysptprof.dbsname database,
> systabnames.tabname indexname,
> keylen,
> round((ti_nptotal* sysshmvals.sh_pagesize)/1024,0)
> totalsize_K,
> sum(lockreqs +
> lockwts +
> deadlks +
> lktouts +
> isreads +
> iswrites +
> isrewrites +
> isdeletes +
> bufreads +
> bufwrites +
> seqscans +
> pagreads +
> pagwrites) total_operations
> from sysptprof, sysptnkey, systabnames, sysshmvals, systabinfo
> where
> sysptnkey.partnum = sysptprof.partnum
> and
> sysptnkey.partnum = systabnames.partnum
> and
> systabnames.partnum = systabinfo.ti_partnum
> group by 1,2,3,4
> order by 5 desc, 4 desc
Some questions and comments remains:
- The manual of IDS 9.40 Administrators Reference says: The sysptprof table
lists information about a tblspace. Tblspaces correspond
to tables. Profile information for a table is available only when a table is
open. When the last user who has a table open closes it, the tblspace in
shared memory is freed, and any profile statistics are lost.
- My tests doesn't aprove that statement. In my case (IDS 9.40FC9W2) the
statistics remains after any connection to the monitored table were closed.
_What is true?_
- Which of the values is significant for the usage of the index? It can't be
all the values because insert, delete or update of rows of the related table
will change some of the values. That has nothing to do with the usage of the
index by queries. My thought is that isreads, pagereads and bufreads may be
significant but it's only a guess.
- The above query (thanks to Mark) should be changed a bit to cope with
fragmented tables too:
/* start here */
database sysmaster;
set isolation dirty read;
select sysptprof.dbsname database,
systabnames.tabname indexname,
keylen,
sum(round((ti_nptotal* sysshmvals.sh_pagesize)/1024,0))
totalsize_K,
sum(lockreqs) slockreqs,
sum(lockwts) slockwts,
sum(deadlks) sdeadlks,
sum(lktouts) slktouts,
sum(isreads) sisrewrites,
sum(iswrites) sisrewrites,
sum(isrewrites) sisrewrites,
sum(isdeletes) sisdeletes,
sum(bufreads) sbufreads,
sum(bufwrites) sbufwrites,
sum(seqscans) sseqscans,
sum(pagreads) spagreads,
sum(pagwrites) spagwrites
from sysptprof, sysptnkey, systabnames, sysshmvals, systabinfo
where
sysptnkey.partnum = sysptprof.partnum
and
sysptnkey.partnum = systabnames.partnum
and
systabnames.partnum = systabinfo.ti_partnum
group by 1,2,3
order by 5 desc, 4 desc
BTW: sysptprof is a view in 9.40 and 10.0 including systabnames, thus
systabnames could be remove from the query.
Any comments are appreciated.
Reinhard.
\