unused indexes
Posted in 2008
tomcaml asked whether Informix has metadata showing when an index was last used. Fernando Nunes said not directly, but detached indexes have partition statistics. Mark Scranton posted a sysmaster query joining sysptprof, sysptnkey, systabnames and systabinfo that sums operation counts per index, noting changes may be needed for IDS 10+ with multiple page sizes. The poster thanked him.
Topics: General Discussion
howdy does informix have system views/meta data that indicates the last time an index was used? thanks tom
tomcaml@gmail.com wrote: > howdy > > does informix have system views/meta data that indicates the last time > an index was used? > > thanks > tom Directly no... But if your indexes are detached (they should be) you can look at their partition stats. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
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
On Apr 1, 12:08 pm, "mark.scran...@gmail.com"
<mark.scran...@gmail.com> wrote:
> 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
SWEET!
mucho thanks, Mark