SQL_FEAT_CTRL
hello all,
according to this article
(http://www.jfmiii.com/informix/?paged=2)
We’ve also added a new algorithm for gathering statistics to use
sampling which avoids having to traverse the entire btree. We’ve gone
to great lengths to make sure we handle skewed data with a proprietory
algorithm. That 300% speed jumps to 2000% and best of all, unless the
data is skewed, the time it takes to get stats on and index remains
fairly constant regardless of the size of the index. That 1M page index
will take you about 30s-50s to get stats. Same for that 5M page index.
No more waiting hours for update statistics to complete.
What do you have to do to take advantage of this?
onmode -wm SQL_FEAT_CTRL=0
Enjoy!
Mr.GrumpyPants
-----------------
however, i cannot find much detail about SQL_FEAT_CTRL.
anyone know of some?
thanks,
tom
That undocumented parameter is mostly used to turn off new features that
may cause performance problems for specific kinds of queries.
For example, setting SQL_FEAT_CTRL=0x04 will disable a particular change in
the optimizer that causes the order of execution of UNION queries to
change. At sites that have queries that depended on the order of execution
to return rows from the first SELECT in the UNION followed by rows from the
second SELECT, etc. the new algorithm broke their code. (Why anyone would
depend on the order that data is returned in without an ORDER BY clause
when the SQL standard EXPLICITLY states that you cannot do so, I will never
understand!)
In this case it is being used to optionally enable a new feature that has
not yet been documented, has no explicit ONCONFIG parameter or UPDATE
STATISTICS option to enable it, nor has it been made the default behavior
yet. It was probably intended as a test of a future feature before Mr.
GrumpyPants spilled the beans.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Apr 17, 2012 at 10:02 AM, <tomcaml@gmail.com> wrote:
> hello all,
> according to this article
> (http://www.jfmiii.com/informix/?paged=2)
>
> We’ve also added a new algorithm for gathering statistics to use
> sampling which avoids having to traverse the entire btree. We’ve gone
> to great lengths to make sure we handle skewed data with a proprietory
> algorithm. That 300% speed jumps to 2000% and best of all, unless the
> data is skewed, the time it takes to get stats on and index remains
> fairly constant regardless of the size of the index. That 1M page index
> will take you about 30s-50s to get stats. Same for that 5M page index.
> No more waiting hours for update statistics to complete.
>
> What do you have to do to take advantage of this?
>
> onmode -wm SQL_FEAT_CTRL=0>
> Enjoy!
>
> Mr.GrumpyPants
>
> -----------------
> however, i cannot find much detail about SQL_FEAT_CTRL.
>
>
> anyone know of some?
>
>
> thanks,
> tom
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
On Tuesday, April 17, 2012 9:56:30 AM UTC-5, Art S. Kagel wrote:
> That undocumented parameter is mostly used to turn off new features that may cause performance problems for specific kinds of queries.
>
> For example, setting SQL_FEAT_CTRL=0x04 will disable a particular change in the optimizer that causes the order of execution of UNION queries to change. At sites that have queries that depended on the order of execution to return rows from the first SELECT in the UNION followed by rows from the second SELECT, etc. the new algorithm broke their code. (Why anyone would depend on the order that data is returned in without an ORDER BY clause when the SQL standard EXPLICITLY states that you cannot do so, I will never understand!)
>
>
>
> In this case it is being used to optionally enable a new feature that has not yet been documented, has no explicit ONCONFIG parameter or UPDATE STATISTICS option to enable it, nor has it been made the default behavior yet. It was probably intended as a test of a future feature before Mr. GrumpyPants spilled the beans.
>
>
>
> Art
>
> Art S. Kagel
> Advanced DataTools (<a href="http://www.advancedatatools.com"; target="_blank">www.advancedatatools.com</a>)
> Blog: <a href="http://informix-myview.blogspot.com/"; target="_blank">http://informix-myview.<WBR>blogspot.com/</a>;
>
>
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
>
>
>
>
>
>
>
> On Tue, Apr 17, 2012 at 10:02 AM, <span dir="ltr"><<a href="mailto:tomcaml@gmail.com" target="_blank">tomcaml@gmail.com</a>></span> wrote:
> <blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
>
> hello all,
>
> according to this article
>
> (<a href="http://www.jfmiii.com/informix/?paged=2"; target="_blank">http://www.jfmiii.com/<WBR>informix/?paged=2</a>)
>
>
>
> We’ve also added a new algorithm for gathering statistics to use
>
> sampling which avoids having to traverse the entire btree. We’ve gone
>
> to great lengths to make sure we handle skewed data with a proprietory
>
> algorithm. That 300% speed jumps to 2000% and best of all, unless the
>
> data is skewed, the time it takes to get stats on and index remains
>
> fairly constant regardless of the size of the index. That 1M page index
>
> will take you about 30s-50s to get stats. Same for that 5M page index.
>
> No more waiting hours for update statistics to complete.
>
>
>
> What do you have to do to take advantage of this?
>
>
>
> onmode -wm SQL_FEAT_CTRL=0>
>
>
> Enjoy!
>
>
>
> Mr.GrumpyPants
>
>
>
> -----------------
>
> however, i cannot find much detail about SQL_FEAT_CTRL.
>
>
>
>
>
> anyone know of some?
>
>
>
>
>
> thanks,
>
> tom
>
> ______________________________<WBR>_________________
>
> Informix-list mailing list
>
> <a href="mailto:Informix-list@iiug.org" target="_blank">Informix-list@iiug.org</a>
>
> <a href="http://www.iiug.org/mailman/listinfo/informix-list"; target="_blank">http://www.iiug.org/mailman/<WBR>listinfo/informix-list</a>;
>
> </blockquote></div>
thank you!
As Art mention it's an undocumented variable. And what this means is that
it really shouldn't be used (and mentioned in blogs) without a proper
recommendation from tech support.
This parameter (and another one) is a bitmap where each bit has a specific
function. So setting it to 0 resets all the bits. This is the default by
the way...
Regarding the topic covered in the blog, you don't need this to control it.
The way to control it it by using the USTLOW_SAMPLE (11.70.FC4) (also
available with SET ENVIRONMENT).
So for this purpose forget about SQL_FEAT_CTRL. And don't use undocumented
features unless tech support tells you (even when they show up on blogs...)
Most (if not all) the bits in SQL_FEAT_CTRL are used to turn off changes
made into the engine that somehow may cause problems for existing uses (Art
gave a very good example), and some of these features are not even
available in currently GA versions.
P.S.: as a user I hate undocumented variables as much as anyone else... We
always get the feeling that we could improve something with them. But in
general there are good reasons for them being undocumented....
Regards
On Tue, Apr 17, 2012 at 8:03 PM, <tomcaml@gmail.com> wrote:
> On Tuesday, April 17, 2012 9:56:30 AM UTC-5, Art S. Kagel wrote:
> > That undocumented parameter is mostly used to turn off new features that
> may cause performance problems for specific kinds of queries.
> >
> > For example, setting SQL_FEAT_CTRL=0x04 will disable a particular change
> in the optimizer that causes the order of execution of UNION queries to
> change. At sites that have queries that depended on the order of execution
> to return rows from the first SELECT in the UNION followed by rows from the
> second SELECT, etc. the new algorithm broke their code. (Why anyone would
> depend on the order that data is returned in without an ORDER BY clause
> when the SQL standard EXPLICITLY states that you cannot do so, I will never
> understand!)
> >
> >
> >
> > In this case it is being used to optionally enable a new feature that
> has not yet been documented, has no explicit ONCONFIG parameter or UPDATE
> STATISTICS option to enable it, nor has it been made the default behavior
> yet. It was probably intended as a test of a future feature before Mr.
> GrumpyPants spilled the beans.
> >
> >
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (<a href="http://www.advancedatatools.com";
> target="_blank">www.advancedatatools.com</a>)
> > Blog: <a href="http://informix-myview.blogspot.com/"; target="_blank">
> http://informix-myview.<WBR>blogspot.com/</a>;
> >
> >
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
> >
> >
> >
> >
> >
> >
> >
> > On Tue, Apr 17, 2012 at 10:02 AM, <span dir="ltr"><<a href="mailto:
> tomcaml@gmail.com" target="_blank">tomcaml@gmail.com</a>></span> wrote:
> > <blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px
> #ccc solid;padding-left:1ex">
> >
> > hello all,
> >
> > according to this article
> >
> > (<a href="http://www.jfmiii.com/informix/?paged=2"; target="_blank">
> http://www.jfmiii.com/<WBR>informix/?paged=2</a>)
> >
> >
> >
> > We’ve also added a new algorithm for gathering statistics to use
> >
> > sampling which avoids having to traverse the entire btree. We’ve gone
> >
> > to great lengths to make sure we handle skewed data with a proprietory
> >
> > algorithm. That 300% speed jumps to 2000% and best of all, unless the
> >
> > data is skewed, the time it takes to get stats on and index remains
> >
> > fairly constant regardless of the size of the index. That 1M page index
> >
> > will take you about 30s-50s to get stats. Same for that 5M page index.
> >
> > No more waiting hours for update statistics to complete.
> >
> >
> >
> > What do you have to do to take advantage of this?
> >
> >
> >
> > onmode -wm SQL_FEAT_CTRL=0> >
> >
> >
> > Enjoy!
> >
> >
> >
> > Mr.GrumpyPants
> >
> >
> >
> > -----------------
> >
> > however, i cannot find much detail about SQL_FEAT_CTRL.
> >
> >
> >
> >
> >
> > anyone know of some?
> >
> >
> >
> >
> >
> > thanks,
> >
> > tom
> >
> > ______________________________<WBR>_________________
> >
> > Informix-list mailing list
> >
> > <a href="mailto:Informix-list@iiug.org" target="_blank">
> Informix-list@iiug.org</a>
> >
> > <a href="http://www.iiug.org/mailman/listinfo/informix-list";
> target="_blank">http://www.iiug.org/mailman/
> <WBR>listinfo/informix-list</a>
> >
> > </blockquote></div>
>
>
>
>
> thank you!
> _______________________________________________
> 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...