Re: Why does this transaction on my IDS 11.70FC2 freeze it up?
Posted in 2011
Topics: Performance & Tuning
You could use an external directive for this query and similar ones.
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 Thu, May 12, 2011 at 1:45 PM, Todd Roy <tmroy@hotmail.com> wrote:
>
> IBM finally got back to me and told me it was a probabe defect and as a
> work-around
> told me to turn off MULTI_INDEX with an optimizer directive.
> ie:
>
> SELECT {+AVOID_MULTI_INDEX(orhanmle)}
> count(*)
> from orhanmle
> where anml_sire_key = 743101
> or anml_dam_key = 743101;
>
> worked great.
>
> Is there any way I can disable it globally? Ie no code changes.
>
>
>
> On Sat, Apr 23, 2011 at 1:37 PM, Todd Roy <tmroy@hotmail.com> wrote:
>
> Given a table with about 28Million rows, I try to do
> a simple transaction :
>
> set explain on;>
> select count(*) from orhanmle where> anml_sire_key = 12345
> or
> anml_dam_key = 5678
>
> QUERY: (OPTIMIZATION TIMESTAMP: 04-23-2011 08:32:30)
> ------
> select count(*) from orhanmle where> anml_sire_key = 12345
> or
> anml_dam_key = 5678
>
>
> Estimated Cost: 8
> Estimated # of Rows Returned: 1
>
> 1) informix.orhanmle: MULTI INDEX PATH (SKIP SCAN) (Key-Only)
>
> (1) Index Name: informix.orhanmle_dkey_i2
> Index Keys (Detached): anml_dam_key (Serial, fragments: ALL)
> Lower Index Filter: informix.orhanmle.anml_dam_key = 5678
> OR
> (2) Index Name: informix.orhanmle_skey_i3
> Index Keys (Detached): anml_sire_key (Serial, fragments: ALL)
> Lower Index Filter: informix.orhanmle.anml_sire_key = 12345
>
>
> Basically the engine completely freezes up, requiring a onclean -k
> to restart it.
>
> The table has had its statistics freshly rebuilt with Art Kagel's
> dostats. I also dropped and recreated all the indexes.
>
> In fact. This occurs with ANY "or" in the where clause.
>
> On our production server (11.50FC5) this transaction and others
> like it are instantaneous!
>
> Thanks,
>
> Todd
>
>
>
>
>
> Signature
>
>
> _______________________________________________
> 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...
>
> _______________________________________________ Informix-list mailing list
> Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
>