 |
IDS Forum
Re: Query works fine in IDS 10 but crashes 11.....
Posted By: Art Kagel
Date: Thursday, 12 August 2010, at 4:04 p.m.
In Response To: RE: Query works fine in IDS 10 but crashes 11..... (Wyza, Jonathon)
Dbschema will only do one or all tables. With myschema (included in
utils2_ak) you can specify MATCHES wildcards to the '-t' option so myschema
-d mydatabase -t 'mo*' to list all tables starting with "mo". Nothing
easier than that available I'm afraid.
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:35 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote:
> 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.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636e1ef44ac5572048da5dea1
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
