Re: Measuring fragmentation / performance

From: "Teemu Nieminen" <Teemu.Nieminen@notieto.com> > >Is there any other easy way to measure Informix db (7.22 or newer) >fragmentation than the following sql-statement: > >SELECT DBSNAME, TABNAME, COUNT(*) EXTENTS,>SUM(SIZE) PAGES FROM SYSEXTENTS >WHERE TABNAME NOT LIKE 'sys%' >AND DBSNAME NOT LIKE 'sys%' >AND TABNAME <> 'TBLSpace' >GROUP BY 1,2 >ORDER BY 3,1,2; (If this gives more than 8 extents per table, then the db >is >fragmented, this is what I have been told.) You may have been told wrong. It's not such a problem any more. Although you should try to minimise extents, you can get away with a lot more than 8 easily. >Another thing: Is there any way to measure the times that application >spends >fetching information from Informix db, or is there a possibility to make >some kind of reports about the throughoutput of the db? Hm. I hesitate to suggest this, because in an OLTP environment, it *will* cause a performance hit, but: try Informix-Spy. It will measure and log query performance (costs and times), which can be very handy. But don't run it for too long, your users will kill you. We run it in a DW environment where having a second or two added to elapsed query time is nothing. HTH. ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com