 |
IDS Forum
RE: Query works fine in IDS 10 but crashes 11.....
Posted By: Wyza, Jonathon
Date: Thursday, 12 August 2010, at 4:37 p.m.
In Response To: Re: Query works fine in IDS 10 but crashes 11..... (Art Kagel)
Thanks,
I've bundled up the schemas/unloads (minus identifying information of course)/queries and sent them on to our support personnel at our vendor who will then open a call with IBM for us. I'll relay the final results when I have them.
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 4:04 PM
To: ids@iiug.org
Subject: Re: Query works fine in IDS 10 but crashes 11..... [20910]
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
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
