fragmentation factor

I am working on a system monitoring tool which allows the capture of information from a system rather than just monitor the data. As part of this exercise I want to somehow indicate how fragmented a dbspace and/or complete IDS system is becoming. Obviously the perfect state is when ALL tablespaces have 1 extent. And equally if all tables have more than a handful of extents we could say that it is very fragmented. If there is no other figure available I would like to propose that we define one which can be measured quite simply. How about FF=((nogt150*10) + (nogt100*5) +(nogt50*2))/no where nogt150 is the number of tables with more than 150 extents, nogt100 is the number of tables with more than 100 extents, etc, and no is the total number of tables. I know it's crude but it can be calculated using quite simple arithmetic. on the sysmaster database select count(*) gt150 from systabinfo where ti_nextns > 150 into tempgt150; select count(*) gt100 from systabinfo where ti_nextns > 100 into tempgt100; select count(*) gt50 from systabinfo where ti_nextns > 50 into tempgt50; select count(*) total from systabinfo into temp total;select (10*gt150)+(5*gt100)+(2*gt50)/total from gt150,gt100,gt50, total; Yes I know the SQL could be improved, but it seems to work quite well. On a small sample of the machines I work on I have seen figures ranging from 5 to 75 and I know that the one with 75 is in urgent need. What do other people think about this calculation, and would people like to propose refinements. regards Malcolm

mweallans@panacea.co.uk wrote: > I am working on a system monitoring tool which allows the capture of > information from a system rather than just monitor the data. > > As part of this exercise I want to somehow indicate how fragmented a > dbspace and/or complete IDS system is becoming. Obviously the perfect > state is when ALL tablespaces have 1 extent. And equally if all tables > have more than a handful of extents we could say that it is very > fragmented. > > If there is no other figure available I would like to propose that we > define one which can be measured quite simply. > > How about > FF=((nogt150*10) + (nogt100*5) +(nogt50*2))/no > > where nogt150 is the number of tables with more than 150 extents, > nogt100 is the number of tables with more than 100 extents, etc, and no > is the total number of tables. So, the total weight for more than 150 extents is 17, for more than 100 but less than or equal to 150 is 7, and for more than 50 but less than or equal to 100 is 2... > I know it's crude but it can be calculated using quite simple > arithmetic. Does this account for fragmented tables? Indexes on fragmented tables? Overall, it is a simple calculation, and as an indicator, might be of some value. We can debate the coefficients and ranges endlessly, but as a general figure of merit, especially if you can empirically validate the ranges, it is worth considering. > on the sysmaster database > > select count(*) gt150 from systabinfo where ti_nextns > 150 into temp> gt150; > select count(*) gt100 from systabinfo where ti_nextns > 100 into temp> gt100; > select count(*) gt50 from systabinfo where ti_nextns > 50 into temp> gt50; > select count(*) total from systabinfo into temp total;> select (10*gt150)+(5*gt100)+(2*gt50)/total from gt150,gt100,gt50, > total; > > Yes I know the SQL could be improved, but it seems to work quite well. > On a small sample of the machines I work on I have seen figures ranging > from 5 to 75 and I know that the one with 75 is in urgent need. > > What do other people think about this calculation, and would people > like to propose refinements. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/