Re: 7.3 Performance with PeopleSoft application
"Thomas J. Girsch" wrote:
>
> Actually, there is a subtle, but important difference in the 7.3
> recommendations vs. the 7.2 recommendations. It involves composite indexes
> like these:
>
> CREATE INDEX idx1 ON tab(a, b, c, d);
> CREATE INDEX idx2 ON tab(a, b, e, f);>
> The 7.2x recommendations I have (from the Performance Tuning training guide)
> say that you should in this case do UPDATE STATISTICS HIGH ON tab(a,b,c,e),
> but individually (one UPDATE STATS for each column = 4 statements). In
> other words, run update stats for all columns in the composite index up to
> the first distinct one (the first one that changes from index to index).
>
> The 7.3x recommendations (as per the PDF file) recommend that you do UPDATE
> STATISTICS HIGH ON tab(a); UPDATE STATISTICS HIGH ON tab(c); and UPDATE
> STATISTICS HIGH ON tab(e); in other words, run UPDATE STATISTICS for the
> lead column in the composite index, and for the first distinct column in
_______________________________________________________________________
The 7.3 documentation says that you can update HIGH on column b, but it
is usually not necessary. I've translated that as meaning it doesn't
hurt and can in rare cases help.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> each composite index. In 7.3, they also say that you can wrap all of the
> columns up into a single UPDATE STATS HIGH statement, which you could not do
> in 7.2x
_______________________________________________________________________
Actually, the way I remember it, both 7.2x and 7.3 are the same in this
regard, that is you can combine columns in a single HIGH update but it
is supposed to be faster to do it column by column.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>
> It's splitting hairs, I know, but there IS a difference. And, as you say,
> in 7.2x, they released several recommendations.
>
> In any case, you'll all be happy to know that I've heard from sources at
> Informix (who wish to remain unnamed) that an upcoming release of the engine
> will come with a program to do the proper UPDATE STATS for you, so you don't
> have to sift through the complicated recommendations.
>
> I know that Informix ATG (advanced technical group) already has a program
> that you can run to generate the SQL scripts you need to do it, based on the
> recommendations.
_______________________________________________________________________
I have a 4gl program that will generate the SQL scripts as per 7.3
recommendations as I understood them, although they are kind of vague.
In the example above, the script will:
update statistics medium on table x distributions only;
update statistics high for table x(a);
update statistics high for table x(b);
update statistics high for table x(c);
update statistics high for table x(e);
update statistics low for table x(d);
update statistics low for table x(f);
update statistics low for table x(g); -- a non index column.
If table x has less than 1000 rows a single update high is issued for
the whole table instead of the above sequence.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>
> Art S. Kagel wrote in message <364B19E2.7843@bloomberg.net>...
> >Thomas J. Girsch wrote:
> >>
> >> The recommendations for running update statistics have changed somewhat
> from
> >> 7.2 to 7.3; If you can use sqexplain to see what the optimizer is doing,
> >
> >Thomas, the instructions you posted from the 7.3 release notes are
> >essentially the same as those in the 7.2 release notes. However,
> >unfortunately there were actually three versions of the 7.2 release
> >notes sent out with various platforms' releases. There was on version
> >that had the same recommendations as 7.1 which were simpler, there was
> >one with the notes you published, and there was even a version with no
> >UPDATE STATS recommendations at all. FYI.
> >
> >Art S. Kagel
________________________________________________________________
John H. Frantz Power-4gl: Extending Informix-4gl
john@rl.is http://www.rl.is/~john/pow4gl.html