RE: Update Statistics

Just note that you can run 40-50 at the same time, but some of them will hang, waiting for others to finish first. You can only have 1 update statistics process per CPUVP actively running at a point in time. -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Doug Conrey Sent: 13 June 2005 07:16 PM To: Andrew Ford; informix-list@iiug.org Subject: RE: Update Statistics We do this all the time. The number of parallel processes that can be usefully added varies widely with hardware and data, but we have found that we generally can run 40-50 update stats statements (low, medium, high on many columns and tables) in parallel and see significantly faster performance than running them serially. DC -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Andrew Ford Sent: Monday, June 13, 2005 10:28 AM To: informix-list@iiug.org Subject: Re: Update Statistics Also, if the order no longer matters, can the three update statistics be run in parallel? And if so, does anyone think this will hurt/help/have no effect on performance? Thanks, Andrew "Andrew Ford" <andrewford@austin.rr.com> wrote in message news:_vgre.43956$PR6.7994@tornado.texas.rr.com... > I'm not sure I agree 100% with running update stats medium on the > entire table. > > Sure, there is no performance penalty with regards to the update stats > run times but the optimizer will be using less accurate medium > statistics for heads of indexes to generate query plans until update > stats high for those columns completes successfully. This may not be > a problem for engines that > do light processing during maintenance windows, but for 24/7 engines > this > *could* be a problem. > > Other than the extra human work needed to generate a column list for update > stats medium, are there any downsides to running update statistics in > the following way for newer versions of IDS that have the new update > stats performance improvements (9.3xC2 and up)? > > update statistics low for <tablename (<column list>); # for every> column in > an index > update statistics medium for <tablename> (<column list>) distributionsonly; > # for every column not in the update stats high > update statistics high for <tablename> (<column list>) distributions> only; # for every column that heads an index and the first differing > columns of composite indexes that begin with the same subset of > columns > > Also, the order in which update statistics is run should no longer > matter since we are not overwriting anything. > > Andrew > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:42A8A5CD.3000204@bloomberg.net... > > 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 > > sending to informix-list sending to informix-list

It is claimed, but I haven't tested, that in 10 the update statistics is much more intelligent which is supposed to mean running parallel stats will not be any quicker Dirk Moolman wrote: > Just note that you can run 40-50 at the same time, but some of them will > hang, waiting for others to finish first. > > You can only have 1 update statistics process per CPUVP actively running > at a point in time. > > > -----Original Message----- > From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] > On Behalf Of Doug Conrey > Sent: 13 June 2005 07:16 PM > To: Andrew Ford; informix-list@iiug.org > Subject: RE: Update Statistics > > We do this all the time. The number of parallel processes that can be > usefully added varies widely with hardware and data, but we have found > that we generally can run 40-50 update stats statements (low, medium, > high on many columns and tables) in parallel and see significantly > faster performance than running them serially. > > DC > > -----Original Message----- > From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] > On Behalf Of Andrew Ford > Sent: Monday, June 13, 2005 10:28 AM > To: informix-list@iiug.org > Subject: Re: Update Statistics > > > Also, if the order no longer matters, can the three update statistics be > run in parallel? And if so, does anyone think this will hurt/help/have > no effect on performance? > > Thanks, > > Andrew > > "Andrew Ford" <andrewford@austin.rr.com> wrote in message > news:_vgre.43956$PR6.7994@tornado.texas.rr.com... > >>I'm not sure I agree 100% with running update stats medium on the >>entire table. >> >>Sure, there is no performance penalty with regards to the update stats > > >>run times but the optimizer will be using less accurate medium >>statistics for heads of indexes to generate query plans until update >>stats high for those columns completes successfully. This may not be >>a problem for engines > > that > >>do light processing during maintenance windows, but for 24/7 engines >>this >>*could* be a problem. >> >>Other than the extra human work needed to generate a column list for > > update > >>stats medium, are there any downsides to running update statistics in >>the following way for newer versions of IDS that have the new update >>stats performance improvements (9.3xC2 and up)? >> >>update statistics low for <tablename (<column list>); # for every>>column > > in > >>an index >>update statistics medium for <tablename> (<column list>) distributions> > only; > >># for every column not in the update stats high >>update statistics high for <tablename> (<column list>) distributions>>only; # for every column that heads an index and the first differing >>columns of composite indexes that begin with the same subset of >>columns >> >>Also, the order in which update statistics is run should no longer >>matter since we are not overwriting anything. >> >>Andrew >> >>"Art S. Kagel" <kagel@bloomberg.net> wrote in message >>news:42A8A5CD.3000204@bloomberg.net... >> >>>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 >> >> > > > > sending to informix-list > > sending to informix-list -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #