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