Re: UPDATE STATS times
Neil Truby reported extremely slow UPDATE STATISTICS (and oncheck -pT) on composite indexes where the second or later column is a DATETIME; the behaviour was not platform-specific, appearing on both HP and Solaris, and an IBM PMR was raised. IBM's view was that a heavily imbalanced index B-tree was to blame; Fernando Nunes added that UPDATE STATISTICS LOW gathers index-level data (leaves, levels, clustering, 2nd min/max) and suggested the real cost comes from non-sequential leaf pages and cache effects, implying an index rebuild would help. The thread ends with further testing and a request to resend a diagnostic query, so no confirmed fix is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
"Superboer" <superboer7@t-online.de> wrote in message
news:7e6db895-9818-4bac-ab04-67e863e872d3@g7g2000yqe.googlegroups.com...
Hello Neil,
>> i would try and rule out HP; try it on linux and see if you get the same
>> results.
Yes, it's the same on Solaris.
In fact, it happens on any composite index where the 2nd or subsequent
column is a DATETIME column. It's not only UPDATE STATISTICS, oncheck -pT
is equally impaired for example.
IBM is heavily involved now, PMR 83917,019,866 refers.
"Neil Truby" <neil.truby@ardenta.com> wrote in message
news:7vvraaFfk7U1@mid.individual.net...
>
> In fact, it happens on any composite index where the 2nd or subsequent
> column is a DATETIME column. It's not only UPDATE STATISTICS, oncheck -pT
> is equally impaired for example.
>
> IBM is heavily involved now, PMR 83917,019,866 refers.
The advice on the PMR is that it is to likely to with a heavy imbalance in
the index B-tree.
This still seems to us like an astonishing impact so we're running some more
tests to try to understand it better.
Can anyone explain why we run update statistics (low) on an index rather
than on the individual columns?
Check John Miller presentation.
UPDATE STATS LOW will collect some information that only makes sense for
indexed columns:
- 2nd greater ans smaller value (syscolumns)
- clustered info (sysindexes)
- number of leaves (sysindexes)
- number of levels (sysindexes - apparently you have a very "tall" index)
I may be missing somethings but documentation and the presentation have more
information.
All this info is used by the optimizer.
Regarding the astonishing impact I really understand your surprise. But in a
recent thread regarding other subject I was also surprised by the effect of
the hardware cache (which is what you're sensing here from my perspective).
>From my point of view it's not only the imbalance and the extra pages, but
the fact that the leaves are not sequential (and they'll probably be after
the index rebuild). You can assert this by running the query I sent. It will
take a long time (similar to the process itself and the oncheck -pT).
Note that it should not affect the index usage (maybe more if you do big
range scans, but that would lead us to the previous thread I mentioned)
Regards.
On Tue, Mar 16, 2010 at 5:08 PM, Neil Truby <neil.truby@ardenta.com> wrote:
> "Neil Truby" <neil.truby@ardenta.com> wrote in message
> news:7vvraaFfk7U1@mid.individual.net...
> >
>
> > In fact, it happens on any composite index where the 2nd or subsequent
> > column is a DATETIME column. It's not only UPDATE STATISTICS, oncheck
> -pT
> > is equally impaired for example.
> >
> > IBM is heavily involved now, PMR 83917,019,866 refers.
>
> The advice on the PMR is that it is to likely to with a heavy imbalance in
> the index B-tree.
> This still seems to us like an astonishing impact so we're running some
> more
> tests to try to understand it better.
>
> Can anyone explain why we run update statistics (low) on an index rather
> than on the individual columns?
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
"Fernando Nunes" <domusonline@gmail.com> wrote in message
news:mailman.21.1268761695.1071.informix-list@iiug.org...
>>
>> From my point of view it's not only the imbalance and the extra pages,
>> but the fact that the leaves are not sequential (and they'll probably be
>> after the index rebuild). You can assert this by running the query I
>> sent. It will take a long time (similar to the process itself and the
>> oncheck -pT).
Query? Is that one you send to Claudio?
cheers
No... The one I included in my email to the list on March 14 (Sunday)
Regards.
On Tue, Mar 16, 2010 at 6:38 PM, Neil Truby <neil.truby@ardenta.com> wrote:
> "Fernando Nunes" <domusonline@gmail.com> wrote in message
> news:mailman.21.1268761695.1071.informix-list@iiug.org...
> >>
> >> From my point of view it's not only the imbalance and the extra pages,
> >> but the fact that the leaves are not sequential (and they'll probably be
> >> after the index rebuild). You can assert this by running the query I
> >> sent. It will take a long time (similar to the process itself and the
> >> oncheck -pT).>
> Query? Is that one you send to Claudio?
>
> cheers
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
"Fernando Nunes" <domusonline@gmail.com> wrote in message news:mailman.22.1268780240.1071.informix-list@iiug.org... No... The one I included in my email to the list on March 14 (Sunday) Regards. Ah. On my news feed even now I see no posts on this subject betwen mine on 12/3/2010 at 2150 and mine again of 16/23/2010 at 1708. As John Carlson says, I believe there is a feed error. Could you possibly kindly resend it please?