Re: Peformance degradation

On Thu, 7 Mar 1996, Tom Hogarty wrote: > Sounds very interesting, your problem. Is it possibe that you have defined a > clustered index on a very dynamic table? No, we know that clustered indexes are usually used for fixed or low usage tables. > I would be interested in seeing some of your > tbconfig, in particular I'd like to know how long your checkpoints are taking. We recently changed our checkpoints from 1800 (30 m) to 500 (8 1/3 m). > When you say reorganization what do > mean?, have you unloaded dropped and recreated the tables involved ? Have you > Exported/Imported the database?. Yes, we did a dbexport of the databases, changed the first extent size to the maximum potential size of the tables and re-imported the databases. > Use the series of tbchecks to examine the database... tbcheck -cc, tbcheck -cr, > tbcheck -ce, tbcheck -cI <dbname> and tbcheck -cD. These should take you some > time. Tried it, everythings is ok on these checks. > When you've finished that, check that the contents of your systables is > intact, use select tabname, npused from systables where npused < 0. It has been > known to throw the optimizer. Haven't tried that one, will do. > Look at the output from the SET EXPLAIN ON statement on > offending/inefficient sql statements. Can you use SET EXPLAIN ON with standard SQL? As we aren't using 4gl in the problem areas, we are using stored procedures as these are much faster. > Maybe you need to seperate your logical logs from the rootdbs. I don't believe > that the problem is with striping. Unfortunately, the original DBA (DataBase 'Acker) chose to use the rootdbs for everything. > When you say that you're sharing the chunk with other non-Informix data > do you mean this beast is on cooked files? As we have mentioned previously in our quest for confidence in our own ideas (I am a new DBA - taught on DSA at Informix UK, not V5.04), the one and only dbspace is on an HP LVM amidst other raw data space. > There are so many possibilities here. For a lot of folks in C.D.I. your > performance problems are like a starting gun, its like...time to go to work. > Cancel your weekend, grab the fine manuals, the Suto, the Lumbley and the FAQ, > if its possible, get the system to yourself and go for it. Oh no! I feel so resposible, I didn't realise we were causing this many problems. > Hope This helps, Thanks, very much for you time and patience. Although we are using Craig Ostrom's e-mail account, we are a bunch of English guys working in Holland, the system has been set up by an American Company called ASD and previously there was no-one here that knew what they were doing with Informix. Because we are new people our management and customer need re-assurance that we are heading in the right direction. Which means we need re-assurance that we are doing the right thing. We are very grateful for your assistance and hope we can someday repay you (like in the Mouse and the Lion story). Stanton Attree, Derek Moore (and others at BancTec UK).