Re: Index usage stats.

In article <6vh1ug$ims$1@news.xmission.com>, paulhoward@home.com writes > >I added an index then did update statistic on our test system with only >me on it. I got the following output. > > > bufreads pagwrites > > 1245366 3881 > >This index was certainly not used and bufreads is over 200X pagwrites. >Do you know why? > SET EXPLAIN ON and look at the sqexplain.out generate....how many more times do I have to repeat this.....This is going in the FAQ!!! >Stefan Weideneder wrote: >> >> paulhoward@home.com wrote: >> > >> > Is there any way I can get usage statistics on indexes. We have a number >> > of tables that look like indexes have been added carelessly and I am >> > sure many of them are not being used. I need to clean these up. >> >> Hi, >> >> I think for all detached indexes as well as for all fragmented >> tables there is a way. This will just work for IDS >> and only if you didn't disable the TABLE-statistics. >> >> dbaccess sysmaster - <<eiei >> >> select bufreads, pagwrites from sysptprof where>> dbsname = "yourdatabase" and tabname = "yourindexname"; >> eiei >> >> If the value for "bufreads" is at least twice as much as >> "pagwrites", then it looks as if the index is used. >> >> Best regards, >> >> Stefan Weideneder > -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care