Benefits of reducing extents and running update stats

I was recently tasked with coming up with preventative maintenance on an Informix database (7.31 on AIX 5.3) for a 3rd party application we run. I noticed a few things: 1: We never run UPDATE STATISTICS 2: one table had 7 exents and another had 8. 3: We never ran any onchecks 4: We didn't monitor logical logs to see if they were filling up. So, I said we should: 1: Run update statistics on a nightly basis. ON the advice of someone else, I did Medium although I'm not convinced that I shouldn't have done High or gone the route of running it on specific columns. 2: Drop/Rebuild the 2 tables with at least 7 extents - for some reason, I recall hearing that performance started to drop at 8 extents - I don't know where this came from, but others seem to agree. 3: Run oncheck once a week - (4 of them actually - oncheck -ce; oncheck -cR -y; oncheck -cD -y; oncheck -cI -y) 4: Send alerts if logical logs exceeded 75% - If they do, I believe there's a good chance someone is not running ontape -c or there is something wacky going on. Any comments/recommendations/etc... on what I said we should do? In particular: 1: I'm wondering about whether Update Stats Medium is the best advisable. Why wouldn't HIGH be better? Or even better than that, choose to run it for specific indexed columns? 2: Where does this notion of 8 extents being too many come from? How big of a hit is it? And if so, shouldn't we address tables approaching that number? I was unable to actualy rebuild the table with 7 extents as users started hitting the system an hour after I was told it would be idle and had been idle. But then I noticed that even though that table had 5500 rows on Friday, it only had a little over 100 today. I have no idea how this table is used, but obviously it is very volatile. But with only between 100 and 5500 rows, how big of a deal is it that it takes up 7 - or even 8 (if it grows) extents? I figure running Update Stats should be a much bigger win on such a volatile table. 3: I was a bit hesitant to add -y to the onchecks, but I figured what else could we do besides say Yes to the prompts. I can't imagine what else to do if there were problems detected, so I went with the -y option. 4: As for filling up logical logs, I was told a couple of years ago they filled up due to someone neglecting to run ontape -c and they had to get Informix involved as there was not enough log space to even roll back. I'd never heard of anything like that before. I know rolling back could sometimes be time-consuming, but they claimed they were stuck. 5: I suppose I could have scrutinized every setting in the onconfig file, but I didn't feel comfortable doing so, especially as everything has been running okay and that would require a lot more research on my part. Without posting the whole thing here, I don't expect much advice on this point, but is there anything in particular you would look for? Thanks for reading this far and for anything you have to offer. - Bill "Martino" Martinez

Bill Martinez said: > I was recently tasked with coming up with preventative maintenance on an > Informix database (7.31 on AIX 5.3) for a 3rd party application we run. > > I noticed a few things: > > 1: We never run UPDATE STATISTICS A lot depends on how dynamic your tables are. If they are pre-loaded with a reasonable spread of data and you update statistics once, you could actually get away with it. > 2: one table had 7 exents and another had 8. This is not necessarily a problem. > 3: We never ran any onchecks That's a badness. > 4: We didn't monitor logical logs to see if they were filling up. Maybe they're getting backed up to /dev/null. Backups are quick, but restores take forever. :o) > So, I said we should: > > 1: Run update statistics on a nightly basis. ON the advice of someone > else, I did Medium although I'm not convinced that I shouldn't have done > High or gone the route of running it on specific columns. No. Medium is the worst of the update statistics options, as it's based on a relatively small row sampling. I've had more pain with USM than any other update stats option. If you weren't doing ANY update stats before, then just do update stats low. > 2: Drop/Rebuild the 2 tables with at least 7 extents - for some reason, > I recall hearing that performance started to drop at 8 extents - I don't > know where this came from, but others seem to agree. Historically it was an issue. I don't think it's an issue with 7.31. > 3: Run oncheck once a week - (4 of them actually - oncheck -ce; oncheck > -cR -y; oncheck -cD -y; oncheck -cI -y) Yes. > 4: Send alerts if logical logs exceeded 75% - If they do, I believe > there's a good chance someone is not running ontape -c or there is > something wacky going on. Check what LTAPEDEV is set to in your onconfig. > Any comments/recommendations/etc... on what I said we should do? > In particular: > > 1: I'm wondering about whether Update Stats Medium is the best > advisable. Why wouldn't HIGH be better? Or even better than that, > choose to run it for specific indexed columns? USM is the WORST choice. In most cases, UPDATE STATISTICS LOW is more than adequate. However, the performance guide does outline the official strategy to follow IF update stats low isn't good enough. Or you can download Art Kagel's dostats.ec from the IIUG website. > 2: Where does this notion of 8 extents being too many come from? How > big of a hit is it? And if so, shouldn't we address tables approaching > that number? I don't think it will be a problem. > I was unable to actualy rebuild the table with 7 extents as users > started hitting the system an hour after I was told it would be idle and > had been idle. But then I noticed that even though that table had 5500 > rows on Friday, it only had a little over 100 today. I have no idea how > this table is used, but obviously it is very volatile. But with only > between 100 and 5500 rows, how big of a deal is it that it takes up 7 - > or even 8 (if it grows) extents? I don't think it will be a problem. > I figure running Update Stats should be a much bigger win on such a > volatile table. Not necessarily. > 3: I was a bit hesitant to add -y to the onchecks, but I figured what > else could we do besides say Yes to the prompts. I can't imagine what > else to do if there were problems detected, so I went with the -y option. Furry muff. :o) > 4: As for filling up logical logs, I was told a couple of years ago > they filled up due to someone neglecting to run ontape -c and they had > to get Informix involved as there was not enough log space to even roll > back. I'd never heard of anything like that before. I know rolling > back could sometimes be time-consuming, but they claimed they were stuck. > > > 5: I suppose I could have scrutinized every setting in the onconfig > file, but I didn't feel comfortable doing so, especially as everything > has been running okay and that would require a lot more research on my > part. > > Without posting the whole thing here, I don't expect much advice on > this point, but is there anything in particular you would look for? Post your whole onconfig file. ;o) -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.