Re: 7.3 Performance with PeopleSoft application
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
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
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.
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