strange optimiser behaviour

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 .

Tony, We 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 the fixes for most of the optimizer problems(we hope!). I would be very careful on putting the 7.31uc5 version in you production environment. Test your queries first if possible and if you really have to put it, well, let the force be with you ! Shushu 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.