FW: FW: FW: Explain (more is better?)

-----Original Message----- From: owner-informix-list@iiug.iiug.org [mailto:owner-informix-list@iiug.iiug.org]On Behalf Of Obnoxio The Clown Sent: Tuesday, December 12, 2000 12:50 PM To: informix-list@iiug.org Subject: Re: FW: FW: Explain (more is better?) In the year of Our Lord Tue, 12 Dec 2000 10:02:36 +0200, "Dirk Moolman" <dirkm@reach.co.za> broke a vow of silence to utter: >If we do, everyone else also missed it. We have a case (related to >performance) open with Tech Support now for about 4 months. We've looked at >HP patches, configuration changes, kernel parameters - the works ....... >Mmmmm....well, I've run plenty of different OLTP systems on HP using OPTCOMPIND>0, and never had a problem. >Have you guys ever done a full dbexport/initialise/dbimport? Not recently no, but we only went live in August this year, so the database wasn't initialised long ago. We unloaded from Online5 and loaded into 9.21FC1 >-----Original Message----- >From: owner-informix-list@iiug.iiug.org > >In the year of Our Lord Mon, 11 Dec 2000 10:22:04 +0200, "Dirk Moolman" ><dirkm@reach.co.za> broke a vow of silence to utter: > >> >>OPTCOMPIND = 0 proved to be VERY bad on our system, and we run OLTP. We >>made some onconfig changes, but after changing OPTCOMPIND to 2, we had a >>very drastic improvement in speed. > >Oh? Sounds like you might be missing something... > >>-----Original Message----- >>From: owner-informix-list@iiug.iiug.org >>[mailto:owner-informix-list@iiug.iiug.org]On Behalf Of David Williams >>Sent: Monday, December 11, 2000 3:59 AM >>To: informix-list@iiug.org >>Subject: Re: Explain (more is better?) >> >> >>In article <fApY5.29470$eT4.2358818@nnrp3.clara.net>, John Berry >><johnberry@clara.net> writes >>>We have a large number of tables > 700 with a large majority in the >range >>>of > 1GB >>> >>>Some of the queries on the database can take a number of minutes >especially >>>when >>>multiple joins are being performed. >>> >>>Now I have always assumed the lower the cost of query the better :-) >>> >>>However I am starting to doubt this when I run a low cost query on a >>number >>>of frequent hit tables it can take upto 2 mins to return data >>>If I re-structure the query the cost goes up to 2500 but data is returned >>in >>>seconds ? >>> >> >> Run you run the full update statistics? Perhaps the stat are out of >> date. >> >>>Would I be right in assuming the cost is based on use of indexes and >amount >>>of data retrieved and not a lot else so a high cost query is not always >>bad. >>> >>>and if so what about OPTCONF hmm 0,1 or 2 >>> >> >> OPTCOMPIND = 0 favors indexes and should be used on OLTP systems. >> >>>:-) >>> >>> >> >>-- >>David Williams >> >

In the year of Our Lord Wed, 13 Dec 2000 07:15:23 +0200, "Dirk Moolman" <dirkm@reach.co.za> broke a vow of silence to utter: >-----Original Message----- >From: owner-informix-list@iiug.iiug.org > >In the year of Our Lord Tue, 12 Dec 2000 10:02:36 +0200, "Dirk Moolman" ><dirkm@reach.co.za> broke a vow of silence to utter: > >>If we do, everyone else also missed it. We have a case (related to >>performance) open with Tech Support now for about 4 months. We've looked >at >>HP patches, configuration changes, kernel parameters - the works ....... > >>Mmmmm....well, I've run plenty of different OLTP systems on HP using >OPTCOMPIND>>0, and never had a problem. > >>Have you guys ever done a full dbexport/initialise/dbimport? > >Not recently no, but we only went live in August this year, so the database >wasn't initialised long ago. We unloaded from Online5 and loaded into >9.21FC1 How?

Sounds like your stats are not updated to recommended levels to include data distributions so the query plans are sub-optimal. Get one of the excellent update statistics utilities from the IIUG Software Repository, like my dostats from the package utils2_ak, and run it against all your databases and tables. If you have old 5.xx UPDATE STATs scripts they are no longer sufficient. Art S. Kagel Dirk Moolman wrote: > > -----Original Message----- > From: owner-informix-list@iiug.iiug.org > [mailto:owner-informix-list@iiug.iiug.org]On Behalf Of Obnoxio The Clown > Sent: Tuesday, December 12, 2000 12:50 PM > To: informix-list@iiug.org > Subject: Re: FW: FW: Explain (more is better?) > > In the year of Our Lord Tue, 12 Dec 2000 10:02:36 +0200, "Dirk Moolman" > <dirkm@reach.co.za> broke a vow of silence to utter: > > >If we do, everyone else also missed it. We have a case (related to > >performance) open with Tech Support now for about 4 months. We've looked > at > >HP patches, configuration changes, kernel parameters - the works ....... > > >Mmmmm....well, I've run plenty of different OLTP systems on HP using > OPTCOMPIND> >0, and never had a problem. > > >Have you guys ever done a full dbexport/initialise/dbimport? > > Not recently no, but we only went live in August this year, so the database > wasn't initialised long ago. We unloaded from Online5 and loaded into > 9.21FC1 > > >-----Original Message----- > >From: owner-informix-list@iiug.iiug.org > > > >In the year of Our Lord Mon, 11 Dec 2000 10:22:04 +0200, "Dirk Moolman" > ><dirkm@reach.co.za> broke a vow of silence to utter: > > > >> > >>OPTCOMPIND = 0 proved to be VERY bad on our system, and we run OLTP. We > >>made some onconfig changes, but after changing OPTCOMPIND to 2, we had a > >>very drastic improvement in speed. > > > >Oh? Sounds like you might be missing something... > > > >>-----Original Message----- > >>From: owner-informix-list@iiug.iiug.org > >>[mailto:owner-informix-list@iiug.iiug.org]On Behalf Of David Williams > >>Sent: Monday, December 11, 2000 3:59 AM > >>To: informix-list@iiug.org > >>Subject: Re: Explain (more is better?) > >> > >> > >>In article <fApY5.29470$eT4.2358818@nnrp3.clara.net>, John Berry > >><johnberry@clara.net> writes > >>>We have a large number of tables > 700 with a large majority in the > >range > >>>of > 1GB > >>> > >>>Some of the queries on the database can take a number of minutes > >especially > >>>when > >>>multiple joins are being performed. > >>> > >>>Now I have always assumed the lower the cost of query the better :-) > >>> > >>>However I am starting to doubt this when I run a low cost query on a > >>number > >>>of frequent hit tables it can take upto 2 mins to return data > >>>If I re-structure the query the cost goes up to 2500 but data is returned > >>in > >>>seconds ? > >>> > >> > >> Run you run the full update statistics? Perhaps the stat are out of > >> date. > >> > >>>Would I be right in assuming the cost is based on use of indexes and > >amount > >>>of data retrieved and not a lot else so a high cost query is not always > >>bad. > >>> > >>>and if so what about OPTCONF hmm 0,1 or 2 > >>> > >> > >> OPTCOMPIND = 0 favors indexes and should be used on OLTP systems. > >> > >>>:-) > >>> > >>> > >> > >>-- > >>David Williams > >> > >