index usage
A user on IDS 7.31.UD4 (AIX) wanted to find which of 16 indexes on a table were actually being used. Suggestions: query sysmaster:sysptprof joined to sysindexes for read counts, or set TBLSPACESTATS=1 and use onstat -g ppf (translating partnums via sysmaster:systabnames), noting the BTREE cleaner inflates activity; onstat -g idxscan exists only from 11.10. The catch: only detached indexes have their own partnums and thus separate stats; attached indexes (common in older/upgraded systems) are lumped in with table data, and no one found a way to get their stats — so for the poster's attached indexes no solution was recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I have a table with at least 16 indices. I need to identify all the ones that are being used or identify the ones that are not being used by the application. Is there a script or any other method to determine the indices usage? Please let me know Thanks! Informix Dynamic Server Version 7.31.UD4 on AIX Please note: this is my first post, so please let me know if you need more information or if I need to formulate the question in a different manner. Thanks -Thinh
The sysptprof view in the sysmaster database has the stats on what operations are done on the tables and indexes. The followoing query ought to do the trick . Change the predicates if you want something else. select substr(a.idxname,1,20) as index , a.idxtype as type , b.isreads as reads from sysindexes a , sysmaster:sysptprof b where a.idxname = b.tabname and b.isreads = 0 order by reads desc, idxname ; Cheers, Dick Snoke IBM Data Management - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "THINH TRAN" <thinh.tran@digitalinsight.com> To: ids@iiug.org Date: 12/09/2008 12:31 PM Subject: index usage [14256] Hi, I have a table with at least 16 indices. I need to identify all the ones that are being used or identify the ones that are not being used by the application. Is there a script or any other method to determine the indices usage? Please let me know Thanks! Informix Dynamic Server Version 7.31.UD4 on AIX Please note: this is my first post, so please let me know if you need more information or if I need to formulate the question in a different manner. Thanks -Thinh ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
If you have TBLSPACESTATS set to 1 you can use onstat -g ppf with two
provisos:
1. You'll have to translate the partnums into index names using
sysmaster:systabnames and
2. All indexes on tables that are actively deleted from get some activity
due to the BTREE Cleaner thread's activities.
In IDS 11.10 and alter you can better use onstat -g idxscan, but that's not
available in 7.31 IB.
FYI past time to upgrade. IDS v7.31 goes out of support later in 2009!
Art
On Tue, Dec 9, 2008 at 12:27 PM, THINH TRAN
<thinh.tran@digitalinsight.com>wrote:
> Hi,
>
> I have a table with at least 16 indices. I need to identify all the ones
> that
> are being used or identify the ones that are not being used by the
> application. Is there a script or any other method to determine the indices
> usage? Please let me know Thanks!
>
> Informix Dynamic Server Version 7.31.UD4 on AIX
>
> Please note: this is my first post, so please let me know if you need more
> information or if I need to formulate the question in a different manner.
> Thanks
>
> -Thinh
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
Richard, Thanks for your response. However, the sysptprof view does not have information on the indices. It does have the table information though. Could it be the version we are using. We are upgrading to 11 soon, but I need to identify this prior to even moving to 11 (which is a separate project). Thank you in advance. -Thinh ------WebKitFormBoundaryAA9XAAEAAA+DAANx
Thinh: In version 11.50 all detached indexes will show index operations in sysmaster sysptprof. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) = THINH TRAN-.... = <> = Sent by: = To ids-bounces@iiug. ids@iiug.org = org = cc = Subj= ect 12/09/2008 04:05 Re: index = PM usage------WebKitFormBoundaryAA9= XAA E.... [14259] = = Please respond to = ids@iiug.org = = = = Richard, Thanks for your response. However, the sysptprof view does not have information on the indices. It does have the table information though. Could it be the version we are using. We are upgrading to 11 soon, but I need= to identify this prior to even moving to 11 (which is a separate project).= Thank you in advance. -Thinh ------WebKitFormBoundaryAA9XAAEAAA+DAANx ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
If your indexes are detached (the default for new indexes create under versions 7.31/9.21 and later) then each index has its own partnum (or more than one) and so is included in the stats in sysptprof. However, if your indexes were created with earlier releases than 7.31 (or were specifically created attached) which were then upgraded in place, then these attached indexes are physically part of the tables' partnums and so the stats for them are there but are simply lumped together with the data page stats and cannot be separated. Art On Tue, Dec 9, 2008 at 7:05 PM, TRAN-.... <THINH> wrote: > Richard, > > Thanks for your response. However, the sysptprof view does not have > information on the indices. It does have the table information though. > Could > it be the version we are using. We are upgrading to 11 soon, but I need to > identify this prior to even moving to 11 (which is a separate project). > Thank > you in advance. > > -Thinh > ------WebKitFormBoundaryAA9XAAEAAA+DAANx > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
2008/12/10 Art Kagel <art.kagel@gmail.com>: > If your indexes are detached (the default for new indexes create under > versions 7.31/9.21 and later) then each index has its own partnum (or more > than one) and so is included in the stats in sysptprof. > > However, if your indexes were created with earlier releases than 7.31 (or > were specifically created attached) which were then upgraded in place, then > these attached indexes are physically part of the tables' partnums and so > the stats for them are there but are simply lumped together with the data > page stats and cannot be separated. > > Art > > On Tue, Dec 9, 2008 at 7:05 PM, TRAN-.... <THINH> wrote: > >> Richard, >> >> Thanks for your response. However, the sysptprof view does not have >> information on the indices. It does have the table information though. >> Could >> it be the version we are using. We are upgrading to 11 soon, but I need to >> identify this prior to even moving to 11 (which is a separate project). >> Thank >> you in advance. >> >> -Thinh >> ------WebKitFormBoundaryAA9XAAEAAA+DAANx >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do > those opinions reflect those of other individuals affiliated with any entity > with which I am affiliated nor those of the entities themselves. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > As the original poster is on 7.31 UD4 I hope there are no detached indexes. There is a major bug in that release that kills performance on detatched indexes on anything other than direct equals. Fixed in UD5 but UD4 must use attached or suffer dire consequences. Keith
I stand corrected. I had not considered the attached indexes. I could not find any source for the data you want for attached indexes. Dick Snoke IBM Data Management - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: THINH TRAN-.... <> To: ids@iiug.org Date: 12/09/2008 07:06 PM Subject: Re: index usage------WebKitFormBoundaryAA9XAAE.... [14259] Richard, Thanks for your response. However, the sysptprof view does not have information on the indices. It does have the table information though. Could it be the version we are using. We are upgrading to 11 soon, but I need to identify this prior to even moving to 11 (which is a separate project). Thank you in advance. -Thinh ------WebKitFormBoundaryAA9XAAEAAA+DAANx ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.