Re: Update Statistics

> dostats.ec -- period! Well of course! I'm just trying to reduce the run time of update statistics. It seems like dostats wants you to generate low stats for the same column multiple times and generate medium stats for some columns, only to have them immediately overwritten by generating high statistics. I tested both update statistics methods last night and the state of systables, syscolumns, sysindexes and dbschema -hd output after each run was identical (well, the partnum and version of the table was different in systables between tests since i dropped and recreated the database for each test). Am I wrong in thinking that to update statistics against a table you only need 3 update statistics statements. low - every column that appears in an index high - every column that heads an index and every column in a composite index that is the first column that differs when multiple indexes have the same columns at the head of the index (i.e. (a, b, c, d) and (a, b, c, e) run high for d and e) medium - every column not included in the high stats Andrew ----- Original Message ----- From: "Obnoxio The Clown" <obnoxio@serendipita.com> To: "Andrew Ford" <andrewford@austin.rr.com> Cc: <informix-list@iiug.org> Sent: Thursday, June 09, 2005 3:57 AM Subject: Re: Update Statistics > > Andrew Ford said: > > For the following table on IDS versions 9.30xC2 and up > > > > create table t ( > > a integer, > > b integer, > > c integer, > > d integer, > > e integer > > )> > > > create unique index idx1 on t (a, b); > > create index idx2 on t (b, c, d); > > create index idx3 on t (b, c, e);> > > > do > > dostats.ec -- period! > > > update statistics low for table t (a, b); > > update statistics low for table t (b, c, d); > > update statistics low for table t (b, c, e);> > > > have the same net effect as > > > > update statistics low for table t (a, b, c, d, e);> > > > ? > > > > and do > > > > update statistics medium for table t distributions only; > > update statistics high for table t (a) distributions only; > > update statistics high for table t (b) distributions only; > > update statistics high for table t (d) distributions only; > > update statistics high for table t (e) distributions only;> > > > have the same net effect as > > > > update statistics medium for table t (c) distributions only; > > update statistics high for table t (a, b, d, e) distributions only;> > > > ? > > > > Sorry if this has been answered before. > > > > Tried the following but no definitive answer was found. > > > > http://www3.software.ibm.com/ibmdl/pub/software/dw/dm/informix/0211desai/0211desai.pdf > > (understand the optimizer) > > http://www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/0203miller.html > > (tuning update statistics) > > http://publibfp.boulder.ibm.com/epubs/pdf/8344.pdf (9.3 performance tuning > > guide) > > http://groups-beta.google.com/group/comp.databases.informix/browse_frm/thread/a852f14f35a0038d/6a2d537ae8d21b02?q=update+statistics+repeated+column&rnum=1&hl=en#6a2d537ae8d21b02 > > (cdi post, but based on 7.21 recommendations) > > > > Also, anyone aware of SET EXPLAIN ON in 9.30.UC2 for update statistics > > only > > writing out 1 char to the sqexplain.out file? > > > > Thanks, > > > > Andrew > > > > > > sending to informix-list > > > > > -- > > Bye now, > Obnoxio > > "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" > - Coluche > > A smile is a gift that is free to the giver and precious to the recipient. > But giving someone the finger is free too, and I find it more personal and > sincere. sending to informix-list

Andrew Ford wrote: >>dostats.ec -- period! OK, here's the answer, and John Miller III will please pipe in if I screw it up. After discussions with John in Denver, I am aware that I have to update the algorithms in dostats for servers at least as new as: 7.31xC3 9.30xC3 Servers older than that SHOULD be using the algorithms in dostats.ec as it is and doing the update stats in bits and pieces NOT as one humongous update statistics statement like the ones Andrew was testing. The short answer is: Yes the resulting stats are the same with the 'old' method and the new one. The difference is that the newer versions of IDS have improved sorting and update statistics algorithms built in that make the 'new' method much more efficient at processing large update statistics requests than the older code was and even more efficient than breaking the commands up like dostats does now. IFF however, you have an older version of IDS, and I see, Andrew, that you have 9.30UC2 which IB is from before the new algorithms were installed, ganging all those columns together will end up performing many more sorts and use much more memory and disk and have to scan the data more often than the older optimized algorithms that dostats currently uses. Probably not on the tiny test tables you are checking, but on a production table with dozens of columns and tens of indexes your method will be slower than the way dostats is performing the functionality. The current algorithm in dostats was developed using the guidelines now found in the Performance Guide and in discussions with several folk in Menlo (including John I think) for IDS 7.12 or so when those recommendations were only found in some versions of the release notes and were even then incomplete. The method and the recommendations were designed to minimize the number of times the data is scanned and the number of sorts the database has to make. In the later versions of IDS, those optimizations are performed even better internally to the engine and the sort algorithms and data gathering algorithms were also improved. Now I just have to find time to make the changes and get my employer to approve continued maintenance of these utils - don't ask... just stay tuned. Art S. Kagel > Well of course! I'm just trying to reduce the run time of update > statistics. It seems like dostats wants you to generate low stats for the > same column multiple times and generate medium stats for some columns, only > to have them immediately overwritten by generating high statistics. > > I tested both update statistics methods last night and the state of > systables, syscolumns, sysindexes and dbschema -hd output after each run was > identical (well, the partnum and version of the table was different in > systables between tests since i dropped and recreated the database for each > test). > > Am I wrong in thinking that to update statistics against a table you only > need 3 update statistics statements. > > low - every column that appears in an index > high - every column that heads an index and every column in a composite > index that is the first column that differs when multiple indexes have the > same columns at the head of the index (i.e. (a, b, c, d) and (a, b, c, e) > run high for d and e) > medium - every column not included in the high stats > > Andrew > > > ----- Original Message ----- > From: "Obnoxio The Clown" <obnoxio@serendipita.com> > To: "Andrew Ford" <andrewford@austin.rr.com> > Cc: <informix-list@iiug.org> > Sent: Thursday, June 09, 2005 3:57 AM > Subject: Re: Update Statistics > > > >>Andrew Ford said: >> >>>For the following table on IDS versions 9.30xC2 and up >>> >>>create table t ( >>> a integer, >>> b integer, >>> c integer, >>> d integer, >>> e integer >>>)>>> >>>create unique index idx1 on t (a, b); >>>create index idx2 on t (b, c, d); >>>create index idx3 on t (b, c, e);>>> >>>do >> >>dostats.ec -- period! >> >> >>>update statistics low for table t (a, b); >>>update statistics low for table t (b, c, d); >>>update statistics low for table t (b, c, e);>>> >>>have the same net effect as >>> >>>update statistics low for table t (a, b, c, d, e);>>> >>>? >>> >>>and do >>> >>>update statistics medium for table t distributions only; >>>update statistics high for table t (a) distributions only; >>>update statistics high for table t (b) distributions only; >>>update statistics high for table t (d) distributions only; >>>update statistics high for table t (e) distributions only;>>> >>>have the same net effect as >>> >>>update statistics medium for table t (c) distributions only; >>>update statistics high for table t (a, b, d, e) distributions only;>>> >>>? >>> >>>Sorry if this has been answered before. >>> >>>Tried the following but no definitive answer was found. >>> >>> > > http://www3.software.ibm.com/ibmdl/pub/software/dw/dm/informix/0211desai/0211desai.pdf > >>>(understand the optimizer) >>> > > http://www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/0203miller.html > >>>(tuning update statistics) >>>http://publibfp.boulder.ibm.com/epubs/pdf/8344.pdf (9.3 performance > > tuning > >>>guide) >>> > > http://groups-beta.google.com/group/comp.databases.informix/browse_frm/thread/a852f14f35a0038d/6a2d537ae8d21b02?q=update+statistics+repeated+column&rnum=1&hl=en#6a2d537ae8d21b02 > >>>(cdi post, but based on 7.21 recommendations) >>> >>>Also, anyone aware of SET EXPLAIN ON in 9.30.UC2 for update statistics >>>only >>>writing out 1 char to the sqexplain.out file? >>> >>>Thanks, >>> >>>Andrew >>> >>> >>>sending to informix-list >>> >> >> >>-- >> >>Bye now, >>Obnoxio >> >>"C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" >> - Coluche >> >>A smile is a gift that is free to the giver and precious to the recipient. >>But giving someone the finger is free too, and I find it more personal and >>sincere. > > > sending to informix-list

Andrew Ford wrote: <SNIP> > Am I wrong in thinking that to update statistics against a table you only > need 3 update statistics statements. > > low - every column that appears in an index > high - every column that heads an index and every column in a composite > index that is the first column that differs when multiple indexes have the > same columns at the head of the index (i.e. (a, b, c, d) and (a, b, c, e) > run high for d and e) > medium - every column not included in the high stats <SNIP> Specific comments: medium - The medium should be done first and it can be done on all columns. Because of the sampling algorithms used it's just as fast to do all columns as to do the subset that's not heading indexes, so dostats just does a MEDIUM on all columns first then overwrites the medium stats with high stats where needed. low - Again, yes for stats quality, no for efficiency if you are running an older release of IDS. high - Same comments as for low. On newer IDSs do it all together, on older ones, break it up. Art S. Kagel