RE: Measuring fragmentation / performance

Posted in 1999

Marichamy, Udaykumar — Mon, 24 May 1999 08:38:36 -0500 — source: Informix-list mailing list archive (1991-1998)

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------ =_NextPart_001_01BEA5EA.B9C35F50 Content-Type: text/plain I bet it is NOT freeware. Check out www.informix.com. Uday -----Original Message----- From: RVASUDEV [mailto:RVASUDEV@inf.com] Sent: Saturday, May 22, 1999 3:34 AM To: 'Obnoxio The Clown'; informix-list@iiug.org Subject: RE: Measuring fragmentation / performance Hi, Is Informix-Spy (mentioned below) freeware / payware ? A URL for it ? TIA Vasudev > -----Original Message----- > From: Obnoxio The Clown [SMTP:obnoxio@hotmail.com] > Sent: Thursday, May 20, 1999 3:01 PM > To: Teemu.Nieminen@notieto.com; informix-list@iiug.org > Subject: 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 ------ =_NextPart_001_01BEA5EA.B9C35F50 Content-Type: text/html Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3DUS-ASCII"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.0.1460.9"> <TITLE>RE: Measuring fragmentation / performance</TITLE> </HEAD> <BODY> I bet it is NOT freeware. Check out = www.informix.com. Uday -----Original Message----- From: RVASUDEV [<A HREF=3D"mailto:RVASUDEV@inf.com" = TARGET=3D"_blank">mailto:RVASUDEV@inf.com</A>] Sent: Saturday, May 22, 1999 3:34 AM To: 'Obnoxio The Clown'; = informix-list@iiug.org Subject: RE: Measuring fragmentation / = performance Hi, Is Informix-Spy (mentioned below) freeware / payware = ? A URL for it ? TIA Vasudev > -----Original Message----- > From:Obnoxio The Clown = [SMTP:obnoxio@hotmail.com] > Sent:Thursday, May 20, 1999 3:01 PM > To:   Teemu.Nieminen@notieto.com; = informix-list@iiug.org > Subject:      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 <A = HREF=3D"http://www.hotmail.com"; TARGET=3D"_blank">http://www.hotmail.com= </A> </BODY> </HTML> ------ =_NextPart_001_01BEA5EA.B9C35F50--