Query works fine in IDS 10 but crashes 11.5 engine
A query joining ~31 tables (4 inner, 27 outer) ran fine on IDS 10 but appeared to hang IDS 11.5 so badly that onmode -z and shutdown failed and oninit had to be killed; no sqexplain output was produced and only CPU, no I/O, was seen. The data had been moved by dbexport/dbimport to a new 64-bit install. Suggestions included refreshing statistics/distributions (AUS, Art Kagel's dostats), recompiling procedures, and building the query up table by table. IBM support and Art Kagel concluded the optimizer was likely exhaustively evaluating an enormous number of join plans rather than truly hanging, and recommended SET OPTIMIZATION LOW plus opening a PMR. No confirmed resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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)
Jonathon Wyza
CX & CBORD System Administrator
CX Programmer/Analyst
Administrative Computing
Bethel College
(574)-257-3381
AIM: Iamwyza
jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu>
==============================
SLES 11x64 & IDS 11.50.FC6
"Don't document the problem, fix it."
- Atli Björgvin Oddsson
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.
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.
Wyza, Jonathon 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. Did you completely drop the statistics when you upgraded? -- 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.
Yup, when we upgraded we had to completely recreate our databases with
dbimport since we changed architectures (32-bit to 64-bit). We didn't import
any of the sys* databases, only the ones that our application uses.
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:21 AM
To: ids@iiug.org
Subject: Re: Query works fine in IDS 10 but crashes 11..... [20874]
Wyza, Jonathon 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.
Did you completely drop the statistics when you upgraded?
--
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.
Wyza, Jonathon wrote:
> Yup, when we upgraded we had to completely recreate our databases with
> dbimport since we changed architectures (32-bit to 64-bit). We didn't import
> any of the sys* databases, only the ones that our application uses.
That's me fucked then.
Log a PMR? It sounds like a bug.
--
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.
But did you run a full update statistics on all databases after rebuilding and
importing all of your data?
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Wyza,
Jonathon
Sent: Thursday, August 12, 2010 9:23 AM
To: ids@iiug.org
Subject: RE: Query works fine in IDS 10 but crashes 11..... [20875]
Yup, when we upgraded we had to completely recreate our databases with
dbimport since we changed architectures (32-bit to 64-bit). We didn't import
any of the sys* databases, only the ones that our application uses.
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:21 AM
To: ids@iiug.org
Subject: Re: Query works fine in IDS 10 but crashes 11..... [20874]
Wyza, Jonathon 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.
Did you completely drop the statistics when you upgraded?
--
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.
When I upgraded from 9 to 11 I had a smilar problem in a small DSS system. The
query at the SUN. Had to whack the oninit daemon. The query is now run through
MGM to control resources. You might set explain on and do a test run to see
how the optimizer wants to hook up. Did your export include the UPDATE STATS
output? Might try running Art's dostats against the table/db as well.
Hello.
Just a tip: there might be some APAR on your FC6 version, did you check it?
Take your optimizer output and post it here, maybe something wrong going
on the optimizer.....
Regards.
Em 12/08/2010 10:38, RALPH GENTRY escreveu:
> When I upgraded from 9 to 11 I had a smilar problem in a small DSS system.
The
> query at the SUN. Had to whack the oninit daemon. The query is now run
through
> MGM to control resources. You might set explain on and do a test run to see
> how the optimizer wants to hook up. Did your export include the UPDATE STATS
> output? Might try running Art's dostats against the table/db as well.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Alexandre Marini
Tecnologia da Informação - DBA
msn: alexandre_marini@hotmail.com
SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix
Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg>
IBM Informix Dynamic Server Certified Professional V10 / V11
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
I'd post what the optimizer has to say, but sadly it never gets that far. It
prints out the last query right before this one (which completes successfully)
and then the oninit process hangs and the optimizer information for the query
in question never gets printed to the file.
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
Alexandre Marini
Sent: Thursday, August 12, 2010 11:07 AM
To: ids@iiug.org
Subject: Re: Query works fine in IDS 10 but crashes.... [20881]
Hello.
Just a tip: there might be some APAR on your FC6 version, did you check it?
Take your optimizer output and post it here, maybe something wrong going on
the optimizer.....
Regards.
Em 12/08/2010 10:38, RALPH GENTRY escreveu:
> When I upgraded from 9 to 11 I had a smilar problem in a small DSS system.
The
> query at the SUN. Had to whack the oninit daemon. The query is now run
through
> MGM to control resources. You might set explain on and do a test run
> to see how the optimizer wants to hook up. Did your export include the
> UPDATE STATS output? Might try running Art's dostats against the table/db as
well.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Alexandre Marini
Tecnologia da Informação - DBA
msn: alexandre_marini@hotmail.com
SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix
Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg>
IBM Informix Dynamic Server Certified Professional V10 / V11
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
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.
Wyza, Jonathon 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)
Being a simple soul, I'd be re-building the sql one table at a time
until it broke.
--
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.
Jonathon wrote:
I'd post what the optimizer has to say, but sadly it never gets that far. It
prints out the last query right before this one (which completes successfully)
and then the oninit process hangs and the optimizer information for the query
in question never gets printed to the file.
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
Response:
You could try running the SQL "set optimization low" prior to executing the
query. As it sounds like the optimizer is doing a too exhaustive search to
find the best query plan, and so the sqlexec thread appears to be hanging
because it's doing some sort of looping code over all the possible join plans
for a 30+ table join. I believe it should have some sort of automatic kick
into low optimization for really big joins so it should not be hanging the
thread like that so you certainly should contact support and try and get them
table schema to try to reproduce the problem to submit a defect. But in the
mean time, set optimization low may help the optimizer rule out some join
plans earlier on so it has less to consider when optimizing. It may or may not
help, but it's probably worth trying.
Jacques Renaut
IBM Informix Advanced Support
APD Team
I did manage to get it to run by eliminate some of the joins. The cost seems
high for it, see below:
QUERY: (OPTIMIZATION TIMESTAMP: 08-12-2010 13:18:23)
------
select id_rec.id
{
id_rec.fullname,
t_clinicals.crs_no,
t_clinicals.sec,
prog_enr_rec.cat,
stu_acad_rec.sess,
stu_acad_rec.yr,
stu_acad_rec.major1,
major_table.txt major_text,
health_rec.physical,
health_rec.sport_phys physical_date,
health_rec.dr_name,
t_cpr.due_date cpr_due,
t_license.due_date license_due,
t_handbook.handbook_completed,
t_healthpr.healthpr_completed,
t_crimhist.crimhist_completed,
case when t_tetnus.stat = "W" then MDY(01,01,2100)
else t_tetnus.immune_date
end last_td_date,
t_tb.immune_date last_tb_date,
t_cxr.immune_date last_cxr_date,
t_rube.immune_date rube_date,
t_rub1.immune_date rub1_date,
t_rub2.immune_date rub2_date,
t_rubt.immune_date rubt_date,
t_meas.immune_date meas_date,
t_mumps.immune_date mumps_date,
case when t_mmr.stat = "W" then MDY(01,01,2100)
else t_mmr.immune_date
end mmr_date,
t_mmr1.immune_date mmr1_date,
t_mmr2.immune_date mmr2_date,
t_varv.immune_date varv_date,
t_vard.immune_date vard_date,
t_vart.immune_date vart_date,
t_hep.immune_date hpbt_date,
t_hep1.immune_date hpb1_date,
t_hep2.immune_date hpb2_date,
t_hep3.immune_date hpb3_date,
today add_date,
nvl(t_has_con.id,0) has_con
}
from id_rec,
outer t_clinicals,
stu_acad_rec,
prog_enr_rec,
outer t_has_con,
major_table,
outer health_rec,
outer ctc_rec t_cpr,
outer ctc_rec t_license,
outer t_handbook,
outer t_healthpr,
outer t_crimhist ,
outer immune_rec t_tetnus,
outer immune_rec t_tb,
outer immune_rec t_cxr,
outer immune_rec t_rube,
outer immune_rec t_rub1,
outer immune_rec t_rub2,
outer immune_rec t_rubt,
outer immune_rec t_meas{,
outer immune_rec t_mumps,
outer immune_rec t_mmr,
outer immune_rec t_mmr1,
outer immune_rec t_mmr2,
outer immune_rec t_varv,
outer immune_rec t_vard,
outer immune_rec t_vart,
outer immune_rec t_hep,
outer immune_rec t_hep1,
outer immune_rec t_hep2,
outer immune_rec t_hep3
}
where stu_acad_rec.sess = "FA"
and stu_acad_rec.yr = 2010
and stu_acad_rec.id = t_clinicals.id
and stu_acad_rec.id = t_has_con.id
and stu_acad_rec.major1 = major_table.major
and stu_acad_rec.reg_stat = "C"
and stu_acad_rec.id = id_rec.id
and (stu_acad_rec.major1 = "47A" or "47A" = " ")
and prog_enr_rec.id = stu_acad_rec.id
and prog_enr_rec.prog = stu_acad_rec.prog
and (major_table.dept = "NUR" or major_table.major = "305")
and major_table.major[1,2] !="50"
and health_rec.id = id_rec.id
and t_cpr.id = id_rec.id
and t_cpr.tick = "NUR"
and t_cpr.resrc = "CPRCERT"
and t_cpr.stat = "E"
and t_license.id = id_rec.id
and t_license.tick = "NUR"
and t_license.resrc = "LICENSE"
and t_license.stat = "E"
and t_handbook.id = id_rec.id
and t_healthpr.id = id_rec.id
and t_crimhist.id = id_rec.id
and t_tetnus.id = id_rec.id
and t_tetnus.immune = "TD"
and t_tetnus.stat in ("A", "W")
and t_tb.id = id_rec.id
and t_tb.immune = "TB"
and t_tb.stat in ("A", "W")
and t_cxr.id = id_rec.id
and t_cxr.immune = "CXR"
and t_cxr.stat in ("A", "W")
and t_rube.id = id_rec.id
and t_rube.immune = "RUBE"
and t_rube.stat in ("A", "W")
and t_rub1.id = id_rec.id
and t_rub1.immune = "RUB1"
and t_rub1.stat in ("A", "W")
and t_rub2.id = id_rec.id
and t_rub2.immune = "RUB2"
and t_rub2.stat in ("A", "W")
and t_rubt.id = id_rec.id
and t_rubt.immune = "RUBT"
and t_rubt.stat in ("A", "W")
and t_meas.id = id_rec.id
and t_meas.immune = "MEAS"
and t_meas.stat in ("A", "W")
{
and t_mumps.id = id_rec.id
and t_mumps.immune = "MUMPS"
and t_mumps.stat in ("A", "W")
and t_mmr.id = id_rec.id
and t_mmr.immune = "MMR"
and t_mmr.stat in ("A", "W")
and t_mmr1.id = id_rec.id
and t_mmr1.immune = "MMR1"
and t_mmr1.stat in ("A", "W")
and t_mmr2.id = id_rec.id
and t_mmr2.immune = "MMR2"
and t_mmr2.stat in ("A", "W")
and t_varv.id = id_rec.id
and t_varv.immune = "VARV"
and t_varv.stat in ("A", "W")
and t_vard.id = id_rec.id
and t_vard.immune = "VARD"
and t_vard.stat in ("A", "W")
and t_vart.id = id_rec.id
and t_vart.immune = "VART"
and t_vart.stat in ("A", "W")
and t_hep.id = id_rec.id
and t_hep.immune = "HPBT"
and t_hep.stat in ("A", "W")
and t_hep1.id = id_rec.id
and t_hep1.immune = "HPB1"
and t_hep1.stat in ("A", "W")
and t_hep2.id = id_rec.id
and t_hep2.immune = "HPB2"
and t_hep2.stat in ("A", "W")
and t_hep3.id = id_rec.id
and t_hep3.immune = "HPB3"
and t_hep3.stat in ("A", "W")
}
Estimated Cost: 1775
Estimated # of Rows Returned: 64
1) informix.major_table: INDEX PATH
(1) Index Name: informix.tmajor_major
Index Keys: major (Key-First) (Serial, fragments: ALL)
Lower Index Filter: informix.major_table.major = '47A'
PostIndex Filter:informix.major_table.dept = 'NUR'
Index Key Filters: (informix.major_table.major[1,2] != '50' )
(2) Index Name: informix.tmajor_major
Index Keys: major (Key-First) (Serial, fragments: ALL)
Lower Index Filter: informix.major_table.major = '305'
PostIndex Filter:informix.major_table.major = '47A'
Index Key Filters: (informix.major_table.major[1,2] != '50' )
2) informix.stu_acad_rec: INDEX PATH
Filters: ((informix.stu_acad_rec.major1 = '47A' AND
informix.stu_acad_rec.reg_stat = 'C' ) AND informix.stu_acad_rec.major1[1,2]
!= '50' )
(1) Index Name: informix.stuac_key2
Index Keys: yr sess id (Serial, fragments: ALL)@@NL@
Maybe it's not hung at all! You said there are 32 tables being joined?
That's over 10^35 query plans that have to be examined! Have you tried the
query running under SET OPTIMIZATION LOW;?? I'll bet it finishes in a
rather short time. As to why 11.50 is different than your earlier release,
I dunno.
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:00 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote:
> I'd post what the optimizer has to say, but sadly it never gets that far.
> It
> prints out the last query right before this one (which completes
> successfully)
> and then the oninit process hangs and the optimizer information for the
> query
> in question never gets printed to the file.
>
> 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
> Alexandre Marini
> Sent: Thursday, August 12, 2010 11:07 AM
> To: ids@iiug.org
> Subject: Re: Query works fine in IDS 10 but crashes.... [20881]
>
> Hello.
> Just a tip: there might be some APAR on your FC6 version, did you check it?
> Take your optimizer output and post it here, maybe something wrong going on
> the optimizer.....
>
> Regards.
> Em 12/08/2010 10:38, RALPH GENTRY escreveu:
> > When I upgraded from 9 to 11 I had a smilar problem in a small DSS
> system.
> The
> > query at the SUN. Had to whack the oninit daemon. The query is now run
> through
> > MGM to control resources. You might set explain on and do a test run
> > to see how the optimizer wants to hook up. Did your export include the
> > UPDATE STATS output? Might try running Art's dostats against the table/db
> as
> well.
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
>
> Alexandre Marini
>
> Tecnologia da Informação - DBA
>
> msn: alexandre_marini@hotmail.com
>
> SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix
>
> Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg>
>
> IBM Informix Dynamic Server Certified Professional V10 / V11
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016e659f4d874e152048da39f5b
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
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.
Cost can only be compared for the identical query with optimizer directives,
data distribution levels, or indexes available changed. They mean nothing
in absolute terms. I've seen queries with costs in the millions that ran in
milliseconds and ones with costs in the hundred that ran for man seconds.
To me, the fact that removing 12 or 15 tables from the query allowed it to
complete in real-time points back to the OPTIMIZATION HIGH versus LOW issue.
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:21 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote:
> I did manage to get it to run by eliminate some of the joins. The cost
> seems
> high for it, see below:
>
> QUERY: (OPTIMIZATION TIMESTAMP: 08-12-2010 13:18:23)
> ------
> select id_rec.id>
> {
>
> id_rec.fullname,
>
> t_clinicals.crs_no,
>
> t_clinicals.sec,
>
> prog_enr_rec.cat,
>
> stu_acad_rec.sess,
>
> stu_acad_rec.yr,
>
> stu_acad_rec.major1,
>
> major_table.txt major_text,
>
> health_rec.physical,
>
> health_rec.sport_phys physical_date,
>
> health_rec.dr_name,
>
> t_cpr.due_date cpr_due,
>
> t_license.due_date license_due,
>
> t_handbook.handbook_completed,
>
> t_healthpr.healthpr_completed,
>
> t_crimhist.crimhist_completed,
>
> case when t_tetnus.stat = "W" then MDY(01,01,2100)
>
> else t_tetnus.immune_date
>
> end last_td_date,
>
> t_tb.immune_date last_tb_date,
>
> t_cxr.immune_date last_cxr_date,
>
> t_rube.immune_date rube_date,
>
> t_rub1.immune_date rub1_date,
>
> t_rub2.immune_date rub2_date,
>
> t_rubt.immune_date rubt_date,
>
> t_meas.immune_date meas_date,
>
> t_mumps.immune_date mumps_date,
>
> case when t_mmr.stat = "W" then MDY(01,01,2100)
>
> else t_mmr.immune_date
>
> end mmr_date,
>
> t_mmr1.immune_date mmr1_date,
>
> t_mmr2.immune_date mmr2_date,
>
> t_varv.immune_date varv_date,
>
> t_vard.immune_date vard_date,
>
> t_vart.immune_date vart_date,
>
> t_hep.immune_date hpbt_date,
>
> t_hep1.immune_date hpb1_date,
>
> t_hep2.immune_date hpb2_date,
>
> t_hep3.immune_date hpb3_date,
>
> today add_date,
>
> nvl(t_has_con.id,0) has_con
>
> }
> from id_rec,
>
> outer t_clinicals,
>
> stu_acad_rec,
>
> prog_enr_rec,
>
> outer t_has_con,
>
> major_table,
>
> outer health_rec,
>
> outer ctc_rec t_cpr,
>
> outer ctc_rec t_license,
>
> outer t_handbook,
>
> outer t_healthpr,
>
> outer t_crimhist ,
>
> outer immune_rec t_tetnus,
>
> outer immune_rec t_tb,
>
> outer immune_rec t_cxr,
>
> outer immune_rec t_rube,
>
> outer immune_rec t_rub1,
>
> outer immune_rec t_rub2,
>
> outer immune_rec t_rubt,
>
> outer immune_rec t_meas{,
>
> outer immune_rec t_mumps,
>
> outer immune_rec t_mmr,
>
> outer immune_rec t_mmr1,
>
> outer immune_rec t_mmr2,
>
> outer immune_rec t_varv,
>
> outer immune_rec t_vard,
>
> outer immune_rec t_vart,
>
> outer immune_rec t_hep,
>
> outer immune_rec t_hep1,
>
> outer immune_rec t_hep2,
>
> outer immune_rec t_hep3
>
> }
> where stu_acad_rec.sess = "FA"
>
> and stu_acad_rec.yr = 2010
>
> and stu_acad_rec.id = t_clinicals.id
>
> and stu_acad_rec.id = t_has_con.id
>
> and stu_acad_rec.major1 = major_table.major
>
> and stu_acad_rec.reg_stat = "C"
>
> and stu_acad_rec.id = id_rec.id
>
> and (stu_acad_rec.major1 = "47A" or "47A" = " ")
>
> and prog_enr_rec.id = stu_acad_rec.id
>
> and prog_enr_rec.prog = stu_acad_rec.prog
>
> and (major_table.dept = "NUR" or major_table.major = "305")
>
> and major_table.major[1,2] !="50"
>
> and health_rec.id = id_rec.id
>
> and t_cpr.id = id_rec.id
>
> and t_cpr.tick = "NUR"
>
> and t_cpr.resrc = "CPRCERT"
>
> and t_cpr.stat = "E"
>
> and t_license.id = id_rec.id
>
> and t_license.tick = "NUR"
>
> and t_license.resrc = "LICENSE"
>
> and t_license.stat = "E"
>
> and t_handbook.id = id_rec.id
>
> and t_healthpr.id = id_rec.id
>
> and t_crimhist.id = id_rec.id
>
> and t_tetnus.id = id_rec.id
>
> and t_tetnus.immune = "TD"
>
> and t_tetnus.stat in ("A", "W")
>
> and t_tb.id = id_rec.id
>
> and t_tb.immune = "TB"
>
> and t_tb.stat in ("A", "W")
>
> and t_cxr.id = id_rec.id
>
> and t_cxr.immune = "CXR"
>
> and t_cxr.stat in ("A", "W")
>
> and t_rube.id = id_rec.id
>
> and t_rube.immune = "RUBE"
>
> and t_rube.stat in ("A", "W")
>
> and t_rub1.id = id_rec.id
>
> and t_rub1.immune = "RUB1"
>
> and t_rub1.stat in ("A", "W")
>
> and t_rub2.id = id_rec.id
>
> and t_rub2.immune = "RUB2"
>
> and t_rub2.stat in ("A", "W")
>
> and t_rubt.id = id_rec.id
>
> and t_rubt.immune = "RUBT"
>
> and t_rubt.stat in ("A", "W")
>
> and t_meas.id = id_rec.id
>
> and t_meas.immune = "MEAS"
>
> and t_meas.stat in ("A", "W")
>
> {
>
> and t_mumps.id = id_rec.id
>
> and t_mumps.immune = "MUMPS"
>
> and t_mumps.stat in ("A", "W")
>
> and t_mmr.id = id_rec.id
>
> and t_mmr.immune = "MMR"
>
> and t_mmr.stat in ("A", "W")
>
> and t_mmr1.id = id_rec.id
>
> and t_mmr1.immune = "MMR1"
>
> and t_mmr1.stat in ("A", "W")
>
> and t_mmr2.id = id_rec.id
>
> and t_mmr2.immune = "MMR2"
>
> and t_mmr2.stat in ("A", "W")
>
> and t_varv.id = id_rec.id
>
> and t_varv.immune = "VARV"
>
> and t_varv.stat in ("A", "W")
>
> and t_vard.id = id_rec.id
>
> and t_vard.immune = "VARD"
>
> and t_vard.stat in ("A", "W")
>
> and t_vart.id = id_rec.id
>
> and t_vart.immune = "VART"
>
> and t_vart.stat in ("A", "W")
>
> and t_hep.id = id_rec.id
>
> and t_hep
Dostats, or rather, for 11.50, dostats_ng.ec, is in the package utils2_ak in
the IIUG Software Repository. There is a BUILDING file that explains how to
modify the makefile for HPUX to build the entire package. Most of the
changes are in the makefile in comments already. But if you only want to
build dostats, just:
esql -o dostats dostats_ng.ec
and poof!
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.
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
******************************************************
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 <ses
Are we bored then Art ?
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
Kagel
Sent: Thursday, August 12, 2010 1: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 q
No, got a migraine and needed a distraction.
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 2:10 PM, Paul Watson <paul@oninit.com> wrote:
> Are we bored then Art ?
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Thursday, August 12, 2010 1: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
> > > > r
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@@
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-----
> > > F
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
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 Ka
#!/bin/ksh
for I in <list tables>
Do
dbschema -q -ss -d database -t $I $I.sqldone
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
Kagel
Sent: Thursday, August 12, 2010 3: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
>
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 op
Jonathon Wyza 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)
============================================================================
Have you compared your IDS10 and IDS11.5 onconfigs for differences that could
impact optimizer functionality or performance?
STACKSIZE, OPTCOMPIND, It's just a guess but might be worth a look if you
haven't already double checked.