Re. Indexes
Question: how to tell whether (and how often) a particular index is actually used by queries. Suggestions: use SET EXPLAIN and grep sqexplain.out for query plans; or, for detached indexes, enable TBLSPACE_STATS, find the index's partition number via oncheck -pT and check onstat -g ppf. The accepted, simplest answer was to query sysmaster:sysptprof (e.g. the isreads column, joined to sysfragments by partnum) for the index name — again only for detached indexes. A follow-up doubt, that inserts/updates on the base table may themselves generate isreads and mask unused indexes, went unanswered.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi Everybody, How do we know the hit on a particular index, example : Table A has index named Z and I want to know how many times "index-Z" was hit by the SQL or it was not hitting at all. Thanks in advance, Sushil..... _________________________________________________________________ MSN 8: Get 6 months for $9.95/month http://join.msn.com/?page=dept/dialup
one way is to set explain on before the query to check the query path. how many times? - probably write a small script to grep in sqexplain.out file rgds preetinder Sushil Shir.... wrote: >Hi Everybody, > >How do we know the hit on a particular index, example : Table A has index >named Z and I want to know how many times "index-Z" was hit by the SQL or it >was not hitting at all. > > >Thanks in advance, >Sushil..... > >_________________________________________________________________ >MSN 8: Get 6 months for $9.95/month http://join.msn.com/?page=dept/dialup > > > >
Hi,
I don't know of a way to count "index hits" by "number of SQL statements
utilizing the index".
But you can do the following for detached indexes (where the index has
it's
own partition number) :
- set TBLSPACE_STATS to 1 in your $ONCONFIG file and
"bounce" the instance. (This will enable collection of tablespace
statistics).
If it's already set to 1 no need to bounce.
- find out the "partition number" of the index you're interested in.
There are several ways, one of which is to (as user "informix")
run "oncheck -pT <database name>:<table name>".
In this output you'll find info about the indexes of the table and from
there you can gather the partition number of the index.
- the collected partition number(s) are in decimal. Convert them to
hexadecimal format.
- run "onstat -g ppf".
This gives you quite a few statistics (read, writes, etc.; see manual
for more info on the output) for each partition that's been used.
First column is the partition number ("partnum") where you find
(i.e. "grep" for) the index's partition number.
If you can't find your index's partition number in the list, that means
it hasn't been used (yet).
This should at least give you a relative idea as to how much a particular
(detached) index is in use.
Regards,
Martin
--
Martin Fuerderer
IBM Informix Development Munich
Data Management Solutions
"Sushil Shir...." <sushilps@hotmail.com>
Sent by: forum.subscriber@iiug.org
02.09.2003 23:57
To: ids@iiug.org
cc:
Subject: Re. Indexes [1792]
Hi Everybody,
How do we know the hit on a particular index, example : Table A has index
named Z and I want to know how many times "index-Z" was hit by the SQL or
it
was not hitting at all.
Thanks in advance,
Sushil.....
_________________________________________________________________
MSN 8: Get 6 months for $9.95/month http://join.msn.com/?page=dept/dialup
I think I snagged this from a bag of tricks (Mark Scranton's I think(?))
downloaded from the IIUG Website (or Mark's old site). It should work on
detached indexes. Give it a try.
unload to 'filename.unl' delimiter " "
select a.tabname[1,18] table,
sum(a.isreads) reads,
sum(a.iswrites) writes,
sum(a.isrewrites) updates,sum(a.isdeletes) deletes
from sysmaster:sysptprof a, <dbname>@<servername>:sysfragments b
where a.tabname not like "sys%" and
a.tabname=b.indexname and
b.partn = a.partnum and
a.dbsname = "<dbname>"
group by tabname
order by reads desc,
writes desc,
updates desc,
deletes desc
Good Luck
"Martin Fuer...."
<MARTINFU@de.ibm. To: ids@iiug.org
com> cc:
Sent by: Subject: Re: Re. Indexes [1795]
forum.subscriber@
iiug.org
09/03/03 08:43 AM
Hi,
I don't know of a way to count "index hits" by "number of SQL statements
utilizing the index".
But you can do the following for detached indexes (where the index has
it's
own partition number) :
- set TBLSPACE_STATS to 1 in your $ONCONFIG file and
"bounce" the instance. (This will enable collection of tablespace
statistics).
If it's already set to 1 no need to bounce.
- find out the "partition number" of the index you're interested in.
There are several ways, one of which is to (as user "informix")
run "oncheck -pT <database name>:<table name>".
In this output you'll find info about the indexes of the table and from
there you can gather the partition number of the index.
- the collected partition number(s) are in decimal. Convert them to
hexadecimal format.
- run "onstat -g ppf".
This gives you quite a few statistics (read, writes, etc.; see manual
for more info on the output) for each partition that's been used.
First column is the partition number ("partnum") where you find
(i.e. "grep" for) the index's partition number.
If you can't find your index's partition number in the list, that means
it hasn't been used (yet).
This should at least give you a relative idea as to how much a particular
(detached) index is in use.
Regards,
Martin
--
Martin Fuerderer
IBM Informix Development Munich
Data Management Solutions
"Sushil Shir...." <sushilps@hotmail.com>
Sent by: forum.subscriber@iiug.org
02.09.2003 23:57
To: ids@iiug.org
cc:
Subject: Re. Indexes [1792]
Hi Everybody,
How do we know the hit on a particular index, example : Table A has index
named Z and I want to know how many times "index-Z" was hit by the SQL or
it
was not hitting at all.
Thanks in advance,
Sushil.....
_________________________________________________________________
MSN 8: Get 6 months for $9.95/month http://join.msn.com/?page=dept/dialup
On Wed, 3 Sep 2003 08:43:21 -0400 (EDT), Martin Fuer.... wrote: >Hi, > >I don't know of a way to count "index hits" by "number of SQL statements >utilizing the index". >But you can do the following for detached indexes (where the index has >it's >own partition number) : > <snip of Martin's techniques> Actually, it'a a lot simpler than that, and it's a method we use here to eliminate under-used indexes, and enables us therefore to apply indexes on a 'batch-job' basis, for removal after. Just do a query on column 'isreads' on sysmaster.sysptprof where dbsname = <your_database> and tabname=<index in question>; this only works with detached indexes. > > -- Malc_p -- XS2Mail: Check your mail anywhere http://www.xs2mail.com/
I used the easiest method which Malc suggested, thanks Malc_p and everybody for all your responses and suggestions. Sushil.... >From: "Malc_p " <malc_p@btinternet.com> >To: ids@iiug.org >Subject: Re: Re. Indexes [1799] Date: Thu, 4 Sep 2003 05:20:22 -0400 >(EDT) >Received: from ace.iiug.org ([216.177.38.212]) by mc10-f3.bay6.hotmail.com >with Microsoft SMTPSVC(5.0.2195.5600); Thu, 4 Sep 2003 02:25:17 -0700 >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org >(8.12.8p1/8.12.8) with ESMTP id h849Q4Vm009377;Thu, 4 Sep 2003 05:26:07 >-0400 (EDT) >Received: (from nobody@localhost)by ace.iiug.org (8.12.8p1/8.12.8/Submit) >id h849KMUs009013;Thu, 4 Sep 2003 05:20:22 -0400 (EDT) >X-Message-Info: vAu4ZEtdRigKlphC6545nUShSgmIkGCx >Message-Id: <200309040920.h849KMUs009013@ace.iiug.org> >Apparently-To: forum.subscriber@iiug.org >Sender: forum.subscriber@iiug.org >Precedence: bulk >Return-Path: nobody@ace.iiug.org >X-OriginalArrivalTime: 04 Sep 2003 09:25:18.0040 (UTC) >FILETIME=[7483ED80:01C372C6] > >On Wed, 3 Sep 2003 08:43:21 -0400 (EDT), Martin Fuer.... wrote: > >Hi, > > > >I don't know of a way to count "index hits" by "number of SQL statements > >utilizing the index". > >But you can do the following for detached indexes (where the index has > >it's > >own partition number) : > > ><snip of Martin's techniques> > >Actually, it'a a lot simpler than that, and it's a method we use here to >eliminate under-used indexes, and enables us therefore to apply indexes on >a 'batch-job' basis, for removal after. >Just do a query on column 'isreads' on sysmaster.sysptprof where dbsname = ><your_database> and tabname=<index in question>; this only works with >detached indexes. > > > > > >-- >Malc_p >-- >XS2Mail: Check your mail anywhere >http://www.xs2mail.com/ > > _________________________________________________________________ Need more e-mail storage? Get 10MB with Hotmail Extra Storage. http://join.msn.com/?PAGE=features/es
Even if an index is never used in a query, would isreads still show up for that index in sysptprof? If there are still inserts, updates, or deletes occurring on the base table, and thus iswrites to that index, aren't there associated isreads as well? I would think it has to read a piece of the index in order to write out a change to it. I'd love to go and propose getting rid of all detatched indexes with zero isreads, but I don't really see any. I've never been sure if we really are using them in queries or not due to this question. Any insights? --John Bejarano, Database Administrator Shutterfly, Inc. --- Malc_p <malc_p@btinternet.com> wrote: > On Wed, 3 Sep 2003 08:43:21 -0400 (EDT), Martin > Fuer.... wrote: > >Hi, > > > >I don't know of a way to count "index hits" by > "number of SQL statements > >utilizing the index". > >But you can do the following for detached indexes > (where the index has > >it's > >own partition number) : > > > <snip of Martin's techniques> > > Actually, it'a a lot simpler than that, and it's a > method we use here to eliminate under-used indexes, > and enables us therefore to apply indexes on a > 'batch-job' basis, for removal after. > Just do a query on column 'isreads' on > sysmaster.sysptprof where dbsname = <your_database> > and tabname=<index in question>; this only works > with detached indexes. > > > > > > -- > Malc_p > -- > XS2Mail: Check your mail anywhere > http://www.xs2mail.com/ > >