RE: analyze_idx utility

I'm glad you enjoyed the program. This may help you a little more. dbaccess sysmaster select dbsname, tabname, pagreads, pagwrites from sysptprof This will show you page I/O against any table - it so happens that detached indexes show up as tables. Other than that I can't think of anything which will show index usage off the top of my head - anyone else? cheers j. > -----Original Message----- > From: Tony Woods [mailto:twoods786@hotmail.co > Sent: Thursday, January 18, 2001 6:33 PM > To: JParker@Engage.com; informix-list@iiug.org > Subject: RE: analyze_idx utility > > > Hi Jack, > > Thanks for your reply. This tool is really very good. I was > finally able to > make it run. It gives a lot of information on the indexes. > But actually what > I was looking for is a list of indexes(index names) which are > in the system, > but are never used or used very very less. I wouldn't care to > create an > index on a column in a table just because that column has > high queries or > joins etc. I generally create an Index on a request of a > developer/user or > when any user says this query is taking a long time. Is there > any this kind > of report in the analyse_idx utility, or is there a way to > configure this > utility to only give a list of Indexes which are not required. > > I would again like to mention that this analyse_idx utility > is very good. > Its really useful for someone who doesn't have any idea of the > system/tables/indexes. > > Thanks again, > > Regards, > Tony. > > > > >From: "Parker, Jack" <JParker@Engage.com> > >To: Tony Woods <twoods786@hotmail.com>, informix-list@iiug.org > >Subject: RE: analyze_idx utility > >Date: Thu, 18 Jan 2001 16:34:22 -0500 > > > > > >Wow, somebody actually downloaded that old thing? I wrote > it years ago. > >There's a fairly complete help file that goes with it - but then it > >probably > >doesn't explain how to compile it. There is a makefile that > comes with do > >a > > > > > > make i4gl_ver - if you are using honest to goodness compiled 4gl > > > > or > > > > make r4gl_ver - is you are using RDS. > > > >It is a utility which gives you a SHOULD BE view of your > indices - not an > >actual. In other words it will scan your source code, look at your > >catalogues, even read your data to determine a 'index > factor' or somesuch - > >in other words a score for what SHOULD be indices. Useful > if you have no > >control or view over the model. You have to manually tell > it to do all > >three of those things - since you may not want it to do one > or more of > >them. > >It was written under 5.0 - before sysmasters came about, it > had no way of > >figuring out what indices are actually used. In other words it is a > >sledgehammer, not a scalpel. > > > >The score is based on: > > > > how many times the column (perferably table.column) occurs in a > >where clause in the code. src_cnt > > degree of uniqueness for the column - this is gated by a page > >threshold - if the table is larger than > > the configurable threshhold, it won't try to scan it, > >otherwise it will read each column for uniqueness. > > Number of potential joins to other tables within the db. > > > >One of the cute things about the program is that it reads > the catalogues > >and > >shows you on the screen what each index is made up of. > > > >Let me know how you get on with it. If you find it halfway > worth while, I > >might even re-visit it with an eye towards bringing it into > the current > >age. > > > >cheers > >j. > > > > > > > > > -----Original Message----- > > > From: Tony Woods [mailto:twoods786@hotmail.com] > > > Sent: Wednesday, January 17, 2001 3:55 PM > > > To: informix-list@iiug.org > > > Subject: analyze_idx utility > > > > > > > > > Hello friends, > > > > > > Has anybody used the analyze_idx utility which gives a report > > > on the Index > > > usage(used/not used/highly used)? Does this utility gives the > > > reliable > > > report? Can anybody please give me the steps to use the > > > utility? I am having > > > a hard time to configure and use it. > > > > > > Thanks in advance for all your replies. > > > _________________________________________________________________ > > > Get your FREE download of MSN Explorer at http://explorer.msn.com > > > > > _________________________________________________________________ > Get your FREE download of MSN Explorer at http://explorer.msn.com >