Re: Update Statistics
Yeah. I've got all of that. But is it true that running update statistics
high (or medium) without the 'distributions only' clause does the work of
low (building statistics) plus some extra stuff (building distributions).
I've been putting a lot of work and thought into optimizing update
statistics for the 9.3xC2 and up engines and I think I'm really really
close.
The general guidelines are
run 'upstats low' for columns in indexes that don't appear in upstats
high
(can get away with this since upstats high will do all of the
things you say about upstats low for the columns as long
as you do not specify 'distributions only')
run 'upstats medium distributions only' for all columns in the table
that do
not appear in upstats high
run 'upstats high' for every column that heads an index and the first
differing columns of composite indexes that begin with the same
subset of columns
(notice I'm leaving off the 'distributions only' here to do the work
that would have been performed by 'upstats low' for these columns)
The main problem I'm trying to resolve now is: update stats low does
another thing besides building statistics. It also sends logically deleted
index pages to the btree cleaner for actual deletion. Which is nice. Does
update statistics high do the same thing for me when i do NOT specify the'distributions only' stuff. If it does, I have a particular table that I
can run update statistics on using only 2 statements (medium and high)
because all of my index columns are either heads of indexes or are the first
differeing column of a composite index that begins with the same subset of
columns, meaning I can possibly eliminate the update statistics low run
because I'm going to do the 'low' work when I run upstats high on them.
Andrew
----- Original Message -----
From: "Doug Conrey" <doug_conrey@oci.com>
To: <informix-list@iiug.org>
Sent: Monday, June 13, 2005 3:23 PM
Subject: RE: Update Statistics
> Update statistics low doesn't build distributions, the way high does. It> compiles information about the structure of the indices (depth, leaf
> nodes, clustered-ness, second-highest and second-lowest values, etc),
> and places them in systables, sysindexes (or sysindices, in IDS 9.4...)
> and syscolumns, whereas medium and high build the distributions of the
> actual data and put it in sysdistrib. Therefore, it is important to run
> low and either medium or high for each column. We generally run low for
> the entire table, high for the leading edge of indices, and medium or
> high (depending on time and data size) for non-leading edge columns.
> Hope that helps.
>
> 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 1:53 PM
> To: informix-list@iiug.org
> Subject: Re: Update Statistics
>
>
> But...
>
> If every column in an index appears in the update statistics high,
> therefore eliminating the need for an update statistics low statement
> all together, is that a bad thing?
>
> Will an update statistics high without the 'distributions only' clause
> scan the indexes and send btree cleaner requests to clean up deleted
> index pages or is update statistics low the only operation that does
> that?
>
> Andrew
>
> "Andrew Ford" <andrewford@austin.rr.com> wrote in message
> news:73hre.43979$PR6.32863@tornado.texas.rr.com...
> > Or...
> >
> > update statistics low for table <table> (<columns>);> > # for every column in an index that doesn't appear in the high run
> >
> > update statistics medium for table <table> (<columns>) distributions> only;
> > # for every column of a table not in the update stats high
> >
> > update statistics high for <tablename> (<column list>);> > # for every column that heads an index and the first differing
> > columns of
> > composite indexes that begin with the same subset of columns
> >
> > moved some colums from the low run to the high run and removed the
> > distributions only clause of the high run.
> >
> > 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
>