unused indexes
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.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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