Update Statistics: Tell me the truth

Update statistics is kinda of an obscure subject with Informix.I can never get a straight answer on the correct way to run it and (most important) WHY ? Besides, there are some optimizer problems with version 7.3x and as far as I know, what the book recommends is not quite valid anymore. I have two questions: 1 - Based on your experience, what is the real recommendation for Update statistics ? (version 7.3x up?) 2 - Is it true that if you have OPTCOMIND set up to 0 you do not have to run update statistics HIGH and MEDIUM ? Thank you guys ! Pati Sent via Deja.com http://www.deja.com/ Before you buy.

Install Client SDK from www.intraware.com - Evaulation Software first. This goes in last AFTER the engine and it freee (nothing to pay, you only pay if you want to also get a support contract) TEN Tools.Engine,Network and oddly the Client SDK qualifies as Network! Then go to www.iiug.org - Software - utils2_ak and compile dostats.ec esql -o dostats dostats,ec Run with dostats -d mydatabase -E and it does it all for you! Set OPTCOMPIND to 0 AND ALSO do update statistics medium/high patricia_br@my-deja.com wrote in message <8rtfg6$npn$1@nnrp1.deja.com>... >Update statistics is kinda of an obscure subject with Informix.>I can never get a straight answer on the correct way to run it and (most >important) WHY ? Besides, there are some optimizer problems with version >7.3x and as far as I know, what the book recommends is not quite valid >anymore. >I have two questions: >1 - Based on your experience, what is the real recommendation for Update >statistics ? (version 7.3x up?) >2 - Is it true that if you have OPTCOMIND set up to 0 you do not have to >run update statistics HIGH and MEDIUM ? > >Thank you guys ! >Pati > > >Sent via Deja.com http://www.deja.com/ >Before you buy.

I've found that the following works quite well : 1. Medium on Table (unless its "small", then HIGH) 2. High on all columns involved in WHERE clauses of queries that you deem as requiring "good" performance. Why? The key to optimization of a query lies in traversing the tables in the correct order using the correct indexes. And, the key to doing that lies in statistics of the tables (how many rows, how large) and the statistics of the columns in comparison with the actual values in the WHERE clause. Put simply, of course... OPTCOMPIND=0 tells Informix to use nested loop joins where possible. This does reduce the need for UPDATE STATISTICS, until the scenario shifts to a multi-table join. There, again, the tables must be traversed in the correct order; Stats will help. Rudy patricia_br@my-deja.com wrote: > Update statistics is kinda of an obscure subject with Informix.> I can never get a straight answer on the correct way to run it and (most > important) WHY ? Besides, there are some optimizer problems with version > 7.3x and as far as I know, what the book recommends is not quite valid > anymore. > I have two questions: > 1 - Based on your experience, what is the real recommendation for Update > statistics ? (version 7.3x up?) > 2 - Is it true that if you have OPTCOMIND set up to 0 you do not have to > run update statistics HIGH and MEDIUM ? > > Thank you guys ! > Pati > > Sent via Deja.com http://www.deja.com/ > Before you buy.

One thing we noticed with 7.3x is any query involving mathematical computations performs as follows With medium stats, maths functions are evaluated first, then other criteria, regardless of whether this makes sense or not (indexes on fields, cardinality, etc etc). With high stats, maths functions are applied in the expected order of complexity. For instance, we had a command that was selecting a bunch of rows from a table, using about 4 criteria, one of which used a mod function on a field (this was a security-thing ... and works very, very fast when the optimiser gets it right). With medium stats, the mod function was applied first ... to every row in the table (150,000,000!), and the query took ages! With high stats, the other criteria, which were well targetted on indexed fields with good cardinality, were evaluated first (as expected), and the resultant intermediate set (of about 20 rows) had the mod function applied to the other field ... and this returned in about seven seconds. Worth thinking about if you use these kinds of queries. Ciao Fuzzy ;-)

patricia_br@my-deja.com wrote: > > Update statistics is kinda of an obscure subject with Informix.> I can never get a straight answer on the correct way to run it and (most > important) WHY ? Besides, there are some optimizer problems with version > 7.3x and as far as I know, what the book recommends is not quite valid > anymore. Use Art Kagel's updstats from the IIUG software archive unless you know enough about updating statistics not to need it (but you would not be asking the question if you fitted into the second catefory). > I have two questions: > 1 - Based on your experience, what is the real recommendation for Update > statistics ? (version 7.3x up?) > 2 - Is it true that if you have OPTCOMIND set up to 0 you do not have to > run update statistics HIGH and MEDIUM ? Pass; other people addressed these questions. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

Jonathan Leffler <jleffler@informix.com> wrote: >Use Art Kagel's updstats from the IIUG software archive unless you know >enough about updating statistics not to need it (but you would not be >asking the question if you fitted into the second catefory). When I ran updstats (thanks Art) on a database with about 1500 tables, it choked. I believe some array reference was out of bounds because of the default size of some data structure(s). Does anybody know what needs to be increased in the src code to allow for this many tables?

On Sat, 14 Oct 2000 13:41:14 GMT, "Z@forget.about.it" <Z@forget.about.it> wrote: >When I ran updstats (thanks Art) on a database with about 1500 tables, it choked. I believe some >array reference was out of bounds because of the default size of some data structure(s). > >Does anybody know what needs to be increased in the src code to allow for this many tables? I don't know offhand, or even if there is such a limitation, but I'm sure Art could tell you. In the meantime though, you could try one of several alternatives to 'dostats', including 'upd_stats' (a perl solution which would require DBI, DBD::Informix, and of course, perl. I assume you already have ESQL/C). It's in the same general area of iiug: http://www.iiug.org/software/index_DBA.html

That should be dostats which is in the package utils2_ak. Art S. Kagel Jonathan Leffler wrote: > > patricia_br@my-deja.com wrote: > > > > Update statistics is kinda of an obscure subject with Informix.> > I can never get a straight answer on the correct way to run it and (most > > important) WHY ? Besides, there are some optimizer problems with version > > 7.3x and as far as I know, what the book recommends is not quite valid > > anymore. > > Use Art Kagel's updstats from the IIUG software archive unless you know > enough about updating statistics not to need it (but you would not be > asking the question if you fitted into the second catefory). > > > I have two questions: > > 1 - Based on your experience, what is the real recommendation for Update > > statistics ? (version 7.3x up?) > > 2 - Is it true that if you have OPTCOMIND set up to 0 you do not have to > > run update statistics HIGH and MEDIUM ? > > Pass; other people addressed these questions. > > -- > Yours, > Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> > Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN > "I don't suffer from insanity; I enjoy every minute of it!"

I don't know about updstats, but my dostats, contained in the package utils2_ak in the IIUG Software Repository, has no known limitations or array bound problems with larger databases unless your machine does not have enough memory. There WAS a hard limit in some older versions, but the versions released in the last year or more use only dynamic structures which are realloc()'d as needed. If anyone DOES hit some such limit PLEASE let me know and I'll fix it, or send me the fix and I'll incorporate it. Art S. Kagel "Z@forget.about.it" wrote: > > Jonathan Leffler <jleffler@informix.com> wrote: > > >Use Art Kagel's updstats from the IIUG software archive unless you know > >enough about updating statistics not to need it (but you would not be > >asking the question if you fitted into the second catefory). > > When I ran updstats (thanks Art) on a database with about 1500 tables, it choked. I believe some > array reference was out of bounds because of the default size of some data structure(s). > > Does anybody know what needs to be increased in the src code to allow for this many tables?

On Mon, 16 Oct 2000 15:40:04 -0400, "Art S. Kagel" <kagel@bloomberg.net> wrote: >That should be dostats which is in the package utils2_ak. > >Art S. Kagel > >Jonathan Leffler wrote: >> Use Art Kagel's updstats from the IIUG software archive unless you know >> enough about updating statistics not to need it (but you would not be >> asking the question if you fitted into the second catefory). Maybe confusing it with my upd_stats :-) , which BTW shouldn't have any limitations either, unless you don't have much memory to begin with :) Cheers, Douglas Wilson

"Z@forget.about.it" <Z@forget.about.it> wrote: >Jonathan Leffler <jleffler@informix.com> wrote: > >>Use Art Kagel's updstats from the IIUG software archive unless you know >>enough about updating statistics not to need it (but you would not be >>asking the question if you fitted into the second catefory). > >When I ran updstats (thanks Art) on a database with about 1500 tables, it choked. I believe some >array reference was out of bounds because of the default size of some data structure(s). > >Does anybody know what needs to be increased in the src code to allow for this many tables? My apologies to Art Kagel: the original paragraph led my faulty memory to make an association between him and updstat(s). I was thinking of Rick Baker's & Jerry Van Zant's "updstat" package. This is the one which choked. I chose this because I don't have Esql/C, but just 4GL.

"Z@forget.about.it" wrote: > "Z@forget.about.it" <Z@forget.about.it> wrote: > >Jonathan Leffler <jleffler@informix.com> wrote: > >>Use Art Kagel's updstats from the IIUG software archive unless you know > >>enough about updating statistics not to need it (but you would not be > >>asking the question if you fitted into the second catefory). > > > >When I ran updstats (thanks Art) on a database with about 1500 tables, > >it choked. I believe some array reference was out of bounds because of > >the default size of some data structure(s). > > > >Does anybody know what needs to be increased in the src code to allow > >for this many tables? > > My apologies to Art Kagel: the original paragraph led my faulty memory > to make an association between him and updstat(s). My apologies for misleading you about the name of Art's program. > I was thinking of Rick Baker's & Jerry Van Zant's "updstat" package. > This is the one which choked. I chose this because I don't have Esql/C, > but just 4GL. If you have the c-code I4GL, then you can use the c4gl script as a substitute for the esql script and can compile and link ESQL/C programs. It isn't ideal; I suspect that the I4GL libraries will lead to bigger programs than the plain ESQL/C libraries, but it will at least work. If you only have the p-code I4GL, then you are stuck. However, ESQL/C comes with ClientSDK, and ClientSDK is a free download from Intraware, so there's no real reason not to be able to use it. Unless you don't have a C compiler either, I suppose, but why are you running a crippled system? -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

Jonathan Leffler <jleffler@informix.com> wrote: >"Z@forget.about.it" wrote: >> "Z@forget.about.it" <Z@forget.about.it> wrote: >> >Jonathan Leffler <jleffler@informix.com> wrote: >> >>Use Art Kagel's updstats from the IIUG software archive unless you know >> >>enough about updating statistics not to need it (but you would not be >> >>asking the question if you fitted into the second catefory). >> > >> >When I ran updstats (thanks Art) on a database with about 1500 tables, >> >it choked. I believe some array reference was out of bounds because of >> >the default size of some data structure(s). >> > >> >Does anybody know what needs to be increased in the src code to allow >> >for this many tables? >> >> My apologies to Art Kagel: the original paragraph led my faulty memory >> to make an association between him and updstat(s). > >My apologies for misleading you about the name of Art's program. > >> I was thinking of Rick Baker's & Jerry Van Zant's "updstat" package. >> This is the one which choked. I chose this because I don't have Esql/C, >> but just 4GL. > >If you have the c-code I4GL, then you can use the c4gl script as a >substitute for the esql script and can compile and link ESQL/C programs. >It isn't ideal; I suspect that the I4GL libraries will lead to bigger >programs than the plain ESQL/C libraries, but it will at least work. >If you only have the p-code I4GL, then you are stuck. However, ESQL/C >comes with ClientSDK, and ClientSDK is a free download from Intraware, >so there's no real reason not to be able to use it. Unless you don't >have a C compiler either, I suppose, but why are you running a crippled >system? Thank you for this info. I do have I4GL and a C-compiler. I'll retry this. I'll also get Intraware's ESQL/C. I appreciate the time you all spend answering questions.