IDS Forum

RE: Query works fine in IDS 10 but crashes 11.....

Posted By: Wyza, Jonathon
Date: Thursday, 12 August 2010, at 3:35 p.m.

In Response To: Re: Query works fine in IDS 10 but crashes 11..... (Art Kagel)

Uhm, is there an easy way to dump the schema of a set of tables?

Jonathon Wyza
CX & CBORD System Administrator
CX Programmer/Analyst
Administrative Computing
Bethel College
(574)-257-3381
AIM: Iamwyza
jonathon.wyza@bethelcollege.edu
==============================
SLES 11x64 & IDS 11.50.FC6

"Don't document the problem, fix it."
- Atli Björgvin Oddsson

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Thursday, August 12, 2010 3:27 PM
To: ids@iiug.org
Subject: Re: Query works fine in IDS 10 but crashes 11..... [20908]

Not out of the realm of possibility, bugs are prolific little critters. Can you package up the SQL, a schema, and either sample data or at least relative row counts and open a support case with IBM? It would be good to know that they are working on a fix for complex queries like this one, especially for you in case the next one you run into can't be broken down so easily.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)

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, Aug 12, 2010 at 3:03 PM, Wyza, Jonathon
<wyzaj@bethelcollege.edu>wrote:

> Art,
> Your information about the way the optimizer is quite enlightening and
> useful
> for understanding why one query will run faster than another. Sadly,
> setting
> the optimization to low didn't help at all. I left it run for about an hour
> before going back and killing Informix. I'm guessing there must be some bug
> in
> 11.5 that handles this level type of situation differently (or wrongly). In
> any case I can break the query down into segments (of maybe 10 joins) as
> temporary tables and then tie them together later in the query.
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Björgvin Oddsson
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Thursday, August 12, 2010 2:05 PM
> To: ids@iiug.org
> Subject: Re: Query works fine in IDS 10 but crashes 11..... [20903]
>
> Oh! SET OPTIMIZATION HIGH/LOW; has nothing to do with the number of rows in
> the tables. In order to determine a query plan under HIGH, the optimizer
> has
> to select the best table to select for the first table to query. It does
> this
> by calculating the costs of selecting each of the 32 tables in your query
> as
> the first table to examine. For each of the 32 selections for the first
> table
> it then has to examine the costs of choosing each table but one as the
> second
> table. For each of these 32*31 options it then calculates the costs of
> choosing each table but two for the third table to join, etc.
> So, the number of calculations it has to perform is the number of tables in
> the query factorial which is a MASSIVE number of calculations. For just 27
> tables the value is:
>
> 10,888,869,450,418,352,160,768,000,000
>
> For 32 tables the number of calculations explodes to:
>
> 263,130,836,933,693,530,167,218,012,160,000,000
>
> 24,165,120 times larger! According to my calculations, if these plans are
> processed one per CPU cycle on a 3.3GHZ machine it will take
> 839,352,209,821,375,589 days or 2,298,014,816,718,847 years to complete, or
> longer than the universe has existed already by two orders of magnitude.
>
> Under LOW optimization, the optimizer only examines one layer or branch of
> possible query plans. So once it has selected the best first table, it only
> looks at the best second table choice give that one choice for the first
> table. That means it only has to examine SUM(1..32) query plans or 561
> plans.
>
> It may be some combination of insufficient distributions and HIGH
> optimization
> that's appearing to hang the server.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors
> (art@iiug.org)
>
> 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, Aug 12, 2010 at 1:29 PM, Wyza, Jonathon
> <wyzaj@bethelcollege.edu>wrote:
>
> > Thanks,
> > Set optimization low isn't helping (that I can tell, it's been running
> > for about 3 minutes now, and when you consider that its only joining
> > against 59 rows that seems excessive). I'm guessing dostats is located at
> iiug?
> > (along, I
> > hope, with a tutorial/detailed readme)
> >
> > Jonathon Wyza
> > CX & CBORD System Administrator
> > CX Programmer/Analyst
> > Administrative Computing
> > Bethel College
> > (574)-257-3381
> > AIM: Iamwyza
> > jonathon.wyza@bethelcollege.edu
> > ==============================
> > SLES 11x64 & IDS 11.50.FC6
> >
> > "Don't document the problem, fix it."
> > - Atli Björgvin Oddsson
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Art Kagel
> > Sent: Thursday, August 12, 2010 1:28 PM
> > To: ids@iiug.org
> > Subject: Re: Query works fine in IDS 10 but crashes 11..... [20898]
> >
> > Personally, I don't think that AUS goes far enough and I depend on my
> > own dostats utility for this. I would try it and see if that makes a
> > difference.
> > That said, see my other post about SET OPTIMIZATION LOW;
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors
> > (art@iiug.org)
> >
> > 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, Aug 12, 2010 at 1:05 PM, Wyza, Jonathon
> > <wyzaj@bethelcollege.edu>wrote:
> >
> > > It really wasn't an "upgrade", it was a fresh install. The steps I
> > > took
> > > we're:
> > >
> > > Old install (ids 10): dbexport -o /tmp cars <transferred the
> > > /tmp/cars.exp files to new box> New install (ids 11.5): dbimport
> > > cars -d dbs1 -i /tmp
> > >
> > > AUS Evaluation Ran (OAT confirms this) AUS Refresh Ran (OAT confirms
> > > this)
> > >
> > > Jonathon Wyza
> > > CX & CBORD System Administrator
> > > CX Programmer/Analyst
> > > Administrative Computing
> > > Bethel College
> > > (574)-257-3381
> > > AIM: Iamwyza
> > > jonathon.wyza@bethelcollege.edu
> > > ==============================
> > > SLES 11x64 & IDS 11.50.FC6
> > >
> > > "Don't document the problem, fix it."
> > > - Atli Björgvin Oddsson
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> > > Of Art Kagel
> > > Sent: Thursday, August 12, 2010 12:10 PM
> > > To: ids@iiug.org
> > > Subject: Re: Query works fine in IDS 10 but crashes 11..... [20888]
> > >
> > > Did you drop all distributions after the upgrade and recreate them
> > > from scratch - also recompiled all stored procedures?
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com) IIUG Board of
> > > Directors
> > > (art@iiug.org)
> > >
> > > 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, Aug 12, 2010 at 10:19 AM, Wyza, Jonathon
> > > <wyzaj@bethelcollege.edu>wrote:
> > >
> > > > I thought that at first, but according to AUS Evaluator, the only
> > > > tables that need statistics updated are tables with less than 100
> > > > rows.
> > > >
> > > > Jonathon Wyza
> > > > CX & CBORD System Administrator
> > > > CX Programmer/Analyst
> > > > Administrative Computing
> > > > Bethel College
> > > > (574)-257-3381
> > > > AIM: Iamwyza
> > > > jonathon.wyza@bethelcollege.edu
> > > > ==============================
> > > > SLES 11x64 & IDS 11.50.FC6
> > > >
> > > > "Don't document the problem, fix it."
> > > > - Atli Björgvin Oddsson
> > > >
> > > > -----Original Message-----
> > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> > > > Of Obnoxio The Clown
> > > > Sent: Thursday, August 12, 2010 10:18 AM
> > > > To: ids@iiug.org
> > > > Subject: Re: Query works fine in IDS 10 but crashes 11.....
> > > > [20872]
> > > >
> > > > Wyza, Jonathon wrote:
> > > > > We have a query that has 4 inner joins and 27 outer joins (I
> > > > > know, it's a
> > > > lot,
> > > > > but that's how it is) . On our IDS 10 machine the query runs
> > > > > with no trouble and very quick (IE we know it doesn't have a
> > > > > Cartesian product). If we run
> > > > it
> > > > > on our IDS 11.5 machine (with the exact same data) then it
> > > > > seizes the
> > > > engine.
> > > > > Killing dbaccess or sacego will not release the session. Doing
> > > > > an onmode -z <sessid> will not work (it just hangs). Shutting
> > > > > down the engine won't work (it just hangs). In the end the only
> > > > > way to get the engine back is to kill
> > > > the
> > > > > oninit process.
> > > > >
> > > > > I've never seen anything like this that would totally consume
> > > > > informix with
> > > > a
> > > > > query. Thoughts? (doing an onstat several times while it is
> > > > > running after you've killed the originating process shows no
> > > > > reads/writes, only cpu usage)
> > > >
> > > > UPDATE STATISTICS?
> > > >
> > > > --
> > > > Cheers,
> > > > Obnoxio The Clown
> > > >
> > > > http://obotheclown.blogspot.com
> > > > I will now proceed to pleasure myself with this fish.
> > > >
> > > > --
> > > > This message has been scanned for viruses and dangerous content by
> > > > OpenProtect(http://www.openprotect.com), and is believed to be
> clean.
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --0016367fb02deba1a0048da2976e
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --002215975ff2d5e377048da3ae98
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --0016e644c2b20c6672048da4333c
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001636e1ef4431998f048da55954

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Query works fine in IDS 10 but crashes 11.5 engine
  Wyza, Jonathon -- Thursday, 12 August 2010, at 10:11 a.m.
  • Re: Query works fine in IDS 10 but crashes 11.5 en
    Obnoxio The Clown -- Thursday, 12 August 2010, at 10:18 a.m.
    • RE: Query works fine in IDS 10 but crashes 11.....
      Wyza, Jonathon -- Thursday, 12 August 2010, at 10:19 a.m.
      • Re: Query works fine in IDS 10 but crashes 11.....
        Obnoxio The Clown -- Thursday, 12 August 2010, at 10:21 a.m.
        • RE: Query works fine in IDS 10 but crashes 11.....
          Wyza, Jonathon -- Thursday, 12 August 2010, at 10:22 a.m.
          • Re: Query works fine in IDS 10 but crashes 11.....
            Obnoxio The Clown -- Thursday, 12 August 2010, at 10:32 a.m.
          • RE: Query works fine in IDS 10 but crashes 11.....
            Plugge, Joe R. -- Thursday, 12 August 2010, at 10:33 a.m.
          • Re: RE: Query works fine in IDS 10 but crashes 11.
            RALPH GENTRY -- Thursday, 12 August 2010, at 10:38 a.m.
            • Re: Query works fine in IDS 10 but crashes....
              Alexandre Marini -- Thursday, 12 August 2010, at 11:07 a.m.
              • RE: Query works fine in IDS 10 but crashes....
                Wyza, Jonathon -- Thursday, 12 August 2010, at 1:00 p.m.
                • Re: RE: Query works fine in IDS 10 but crashes....
                  JACQUES RENAUT -- Thursday, 12 August 2010, at 1:18 p.m.
                  • RE: RE: Query works fine in IDS 10 but crashes....
                    Wyza, Jonathon -- Thursday, 12 August 2010, at 1:21 p.m.
                    • Re: RE: Query works fine in IDS 10 but crashes....
                      Art Kagel -- Thursday, 12 August 2010, at 1:33 p.m.
                • Re: Query works fine in IDS 10 but crashes....
                  Art Kagel -- Thursday, 12 August 2010, at 1:24 p.m.
      • Re: Query works fine in IDS 10 but crashes 11.....
        Art Kagel -- Thursday, 12 August 2010, at 12:09 p.m.
        • RE: Query works fine in IDS 10 but crashes 11.....
          Wyza, Jonathon -- Thursday, 12 August 2010, at 1:05 p.m.
          • Re: Query works fine in IDS 10 but crashes 11.....
            Obnoxio The Clown -- Thursday, 12 August 2010, at 1:09 p.m.
          • Re: Query works fine in IDS 10 but crashes 11.....
            Art Kagel -- Thursday, 12 August 2010, at 1:27 p.m.
            • RE: Query works fine in IDS 10 but crashes 11.....
              Wyza, Jonathon -- Thursday, 12 August 2010, at 1:29 p.m.
              • Re: Query works fine in IDS 10 but crashes 11.....
                Art Kagel -- Thursday, 12 August 2010, at 1:37 p.m.
              • Re: Query works fine in IDS 10 but crashes 11.....
                Art Kagel -- Thursday, 12 August 2010, at 2:04 p.m.
                • RE: Query works fine in IDS 10 but crashes 11.....
                  Paul Watson -- Thursday, 12 August 2010, at 2:10 p.m.
                  • Re: Query works fine in IDS 10 but crashes 11.....
                    Art Kagel -- Thursday, 12 August 2010, at 2:13 p.m.
                • RE: Query works fine in IDS 10 but crashes 11.....
                  Wyza, Jonathon -- Thursday, 12 August 2010, at 3:03 p.m.
                  • Re: Query works fine in IDS 10 but crashes 11.....
                    Art Kagel -- Thursday, 12 August 2010, at 3:27 p.m.
                    • RE: Query works fine in IDS 10 but crashes 11.....
                      Wyza, Jonathon -- Thursday, 12 August 2010, at 3:35 p.m.
                      • Re: Query works fine in IDS 10 but crashes 11.....
                        Art Kagel -- Thursday, 12 August 2010, at 4:04 p.m.
                        • RE: Query works fine in IDS 10 but crashes 11.....
                          Paul Watson -- Thursday, 12 August 2010, at 4:13 p.m.
                        • RE: Query works fine in IDS 10 but crashes 11.....
                          Wyza, Jonathon -- Thursday, 12 August 2010, at 4:37 p.m.
                          • RE: Query works fine in IDS 10 but crashes 11.....
                            Wyza, Jonathon -- Wednesday, 1 September 2010, at 6:54 p.m.
                            • RE: Query works fine in IDS 10 but crashes 11.....
                              Wyza, Jonathon -- Friday, 10 September 2010, at 1:37 p.m.
  • Re: Query works fine in IDS 10 but crashes 11.5 en
    DAVE GRIFFEN -- Friday, 13 August 2010, at 11:41 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.