strange optimizer behavior

Hello all I suspect I may be victim to this bug, I'm 7.31UC5 on AIX 4.3... Does anyone have a bug number that I can reference on Informix's Product Defect Query? Thanks Bryan Tony, We have just recently upgraded the Informix version from 7.24uc5 to 7.31uc5. Big mistake! The batch queries became really slow. Some of the queries that used to run in 6 min started to run in 6 hours ! The only solution was using the directives. Informix has confirmed us that 7.31uc5 version has many optimizer problems, the update statistics medium and high produced not quite accurate distribution information and other interesting problems. We are anxiously waiting for the 7.31uc6 version that should have most of the fixes in it. I would be very careful on putting 7.31 uc5 in your production environment. Try to wait until 7.31uc6 come out and if you really can not, well, let the force be with you... In article <954519765.28049.0.nnrp-10.c1ed1f69@news.demon.co.uk>, "Tony Flaherty" <aef@mfs.misys.co.uk> wrote: > I have two machines, Hp-Ux 10.20 > > The production machine is running IDS 7.30.uc7, the dev machine 7.31.uc5. > I'm trialing KAIO on the dev machine and the ONCONFIG reflects the much > reduced capacity of the dev machine. > > Below is the sql explain output for the same query on both machines. the > data on the dev machine is a month old copy of the data from the live > machine, there are no significant changes in volume or distribution between > the two. Table site_addr has approx. 2,500 rows, detail has 80,000 rows. > > I've used Arts dostats to update statistics on both machines. > > 7.31.uc5 dev machine;- > > QUERY: (FIRST_ROWS OPTIMIZATION) > > ------ > SELECT COUNT(UNIQUE de.site_serial) no_sites > FROM detail de,> site_addr sa > WHERE sa.site_serial = de.site_serial > AND sa.site_status = "C" > AND sa.site_cat = "C" > AND de.det_status = "C" > AND de.prod_code = "PRI0111" > > Estimated Cost: 28902 > Estimated # of Rows Returned: 1 > > 1) aef.sa: SEQUENTIAL SCAN > > Filters: (aef.sa.site_status = 'C' AND aef.sa.site_cat = 'C' ) > > 2) aef.de: INDEX PATH > > Filters: (aef.de.det_status = 'C' AND aef.de.prod_code = 'PRI0111' ) > > (1) Index Keys: site_serial > Lower Index Filter: aef.de.site_serial = aef.sa.site_serial > NESTED LOOP JOIN > > 7.30.uc7 live machine;- > > QUERY: (FIRST_ROWS OPTIMIZATION) > > ------ > SELECT COUNT(UNIQUE de.site_serial) no_sites > FROM detail de,> site_addr sa > WHERE sa.site_serial = de.site_serial > AND sa.site_status = "C" > AND sa.site_cat = "C" > AND de.det_status = "C" > AND de.prod_code = "PRI0111" > > Estimated Cost: 819 > Estimated # of Rows Returned: 1 > > 1) aef.de: INDEX PATH > > Filters: aef.de.det_status = 'C' > > (1) Index Keys: prod_code > Lower Index Filter: aef.de.prod_code = 'PRI0111' > > 2) aef.sa: INDEX PATH > > Filters: (aef.sa.site_status = 'C' AND aef.sa.site_cat = 'C' ) > > (1) Index Keys: site_serial > Lower Index Filter: aef.sa.site_serial = aef.de.site_serial > NESTED LOOP JOIN > > Why is the dev machine (7.31.uc5) taking the wrong route? If I add > optimiser directives {+ORDERED} or {+ALL_ROWS} it works correctly. > > I've had problems with some other moderately more complex SQL (4GL) code I > was working on and fixed this with optimiser directives, assuming the > problems were due to the complexity of the SQL but now I'm not so sure. > > Has anyone else had optimiser problems with 7.31.uc5? > > -- > --------------------------------------- > Tony Flaherty aef@mfs.misys.co.uk > Analyst Programmer > Misys Financial Systems > All statements and opinions are my own, > Misys don't pay me enough to have opinions > on their behalf > > . > > Sent via Deja.com http://www.deja.com/ Before you buy.