Re: Optimizer Issues with 10.00.xC8
A user on IDS 10.00.xC8 found that running the recommended "detailed" UPDATE STATISTICS sequence (MEDIUM DISTRIBUTIONS ONLY on some columns, HIGH on indexed columns, LOW on the composite index) actually wiped the colmin/colmax values in syscolumns for the first column of the unique composite index — values that a plain UPDATE STATISTICS LOW FOR TABLE had populated. Art Kagel agreed this looks like a bug, since HIGH/LOW should refresh those low-level column stats, and advised opening a support case; another poster also asked for the PMR number and noted other 10.00.xC6/xC7 defects. No fix or PMR outcome is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Another point of confusion that has me summoning the UPDATE STATISTICS
gods. As a reminder, my test table ("foo") looks consists of five
columns, col1-col5. Col1, Col2, and Col3 are integers, while Col4 and
Col5 are character fields. I have a unique index on (Col1, Col3) and a
nonunique index on (Col2).
If I run:
UPDATE STATISTICS LOW FOR TABLE foo;
...then I get values in levels, leaves, nunique, and cluster in
sysindexes for both indexes, as well as values in colmin and colmax in
syscolumns for Col1 and Col2.
But if I run the "detailed" stats, as per the performance guide:
UPDATE STATISTICS MEDIUM FOR TABLE foo(Col3, Col4, Col5) DISTRIBUTIONS ONLY;
UPDATE STATISTICS HIGH FOR TABLE foo(Col1);
UPDATE STATISTICS HIGH FOR TABLE foo(Col2);
UPDATE STATISTICS LOW FOR TABLE foo(Col1, Col3);
...then I get distributions (of course) and all the values I listed
above, EXCEPT that now colmin and colmax have been blanked out for Col1.
WTF?
Why should detailed stats offer LESS information in syscolumns than
low-only stats? And just to be clear, in my tests I ran the detailed
stats AFTER I had run just low for the table, so the detailed stats
actively REMOVE the values from colmin and colmax for Col1. The colmin
and colmax values for Col2 stay put in all cases.
tgirsch wrote:
> Another point of confusion that has me summoning the UPDATE STATISTICS
> gods. As a reminder, my test table ("foo") looks consists of five
> columns, col1-col5. Col1, Col2, and Col3 are integers, while Col4 and
> Col5 are character fields. I have a unique index on (Col1, Col3) and a
> nonunique index on (Col2).
>
> If I run:
>
> UPDATE STATISTICS LOW FOR TABLE foo;>
> ...then I get values in levels, leaves, nunique, and cluster in
> sysindexes for both indexes, as well as values in colmin and colmax in
> syscolumns for Col1 and Col2.
>
> But if I run the "detailed" stats, as per the performance guide:
>
> UPDATE STATISTICS MEDIUM FOR TABLE foo(Col3, Col4, Col5) DISTRIBUTIONS ONLY;
> UPDATE STATISTICS HIGH FOR TABLE foo(Col1);
> UPDATE STATISTICS HIGH FOR TABLE foo(Col2);
> UPDATE STATISTICS LOW FOR TABLE foo(Col1, Col3);>
> ...then I get distributions (of course) and all the values I listed
> above, EXCEPT that now colmin and colmax have been blanked out for Col1.
>
I agree, that's a bug. In his paper on Update Statistics John Miller
III specifically states that the HIGH on col1 should have refreshed the
low level stats in syscolumns for col1 and the LOW should certainly have
done the same for both col1 & col3. Call tech support and open a case.
This might explain several strange performance issues with 10.00 that
I'm aware of, now I have to go look at syscolumns on a few servers
<sigh>. Please post the PMR when you have it.
Art S. Kagel
Oninit
> WTF?
>
> Why should detailed stats offer LESS information in syscolumns than
> low-only stats? And just to be clear, in my tests I ran the detailed
> stats AFTER I had run just low for the table, so the detailed stats
> actively REMOVE the values from colmin and colmax for Col1. The colmin
> and colmax values for Col2 stay put in all cases.
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
> ===========================================================================================
> Please access the attached hyperlink for an important electronic communications disclaimer:
>
> http://www.oninit.com/home/disclaimer.php
>
> ===========================================================================================
>
>
>
===========================================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.oninit.com/home/disclaimer.php
===========================================================================================
On 13 Mar, 03:14, "Art S. Kagel (Oninit)" <a...@oninit.com> wrote:
> tgirsch wrote:
> > Another point of confusion that has me summoning the UPDATE STATISTICS
> > gods. As a reminder, my test table ("foo") looks consists of five
> > columns, col1-col5. Col1, Col2, and Col3 are integers, while Col4 and
> > Col5 are character fields. I have a unique index on (Col1, Col3) and a
> > nonunique index on (Col2).
>
> > If I run:
>
> > UPDATE STATISTICS LOW FOR TABLE foo;>
> > ...then I get values in levels, leaves, nunique, and cluster in
> > sysindexes for both indexes, as well as values in colmin and colmax in
> > syscolumns for Col1 and Col2.
>
> > But if I run the "detailed" stats, as per the performance guide:
>
> > UPDATE STATISTICS MEDIUM FOR TABLE foo(Col3, Col4, Col5) DISTRIBUTIONS ONLY;
> > UPDATE STATISTICS HIGH FOR TABLE foo(Col1);
> > UPDATE STATISTICS HIGH FOR TABLE foo(Col2);
> > UPDATE STATISTICS LOW FOR TABLE foo(Col1, Col3);>
> > ...then I get distributions (of course) and all the values I listed
> > above, EXCEPT that now colmin and colmax have been blanked out for Col1.
>
> I agree, that's a bug. In his paper on Update Statistics John Miller
> III specifically states that the HIGH on col1 should have refreshed the
> low level stats in syscolumns for col1 and the LOW should certainly have
> done the same for both col1 & col3. Call tech support and open a case.
> This might explain several strange performance issues with 10.00 that
> I'm aware of, now I have to go look at syscolumns on a few servers
> <sigh>. Please post the PMR when you have it.
>
> Art S. Kagel
> Oninit
>
>
>
>
>
> > WTF?
>
> > Why should detailed stats offer LESS information in syscolumns than
> > low-only stats? And just to be clear, in my tests I ran the detailed
> > stats AFTER I had run just low for the table, so the detailed stats
> > actively REMOVE the values from colmin and colmax for Col1. The colmin
> > and colmax values for Col2 stay put in all cases.
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list
>
> > ===========================================================================================
> > Please access the attached hyperlink for an important electronic communications disclaimer:
>
> >http://www.oninit.com/home/disclaimer.php
>
> > ===========================================================================================
>
> ===========================================================================================
> Please access the attached hyperlink for an important electronic communications disclaimer:
>
> http://www.oninit.com/home/disclaimer.php
>
> ===========================================================================================- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
Yes please let us know the PMR.
10.00.xC6 ontape restore did not work
10.00.xC7 had two flashes about it returning wrong results and also
IC54416: ALTER FRAGMENT ON INDEX CAUSES ASSERT FAILED MEMORY
POINTER FREED TWICE
Apparrently 10.00.xC8W1 is out now...