Performance on Informix 12.10.FC6

Last weekend we upgraded our Primary and Secondary HDR servers from 11.70.FC7 to 12.10.FC6. There have been a couple of performance drops noticed so for and am looking for some general guidance here. The config. param values we have in version 11 have been retained in v12. The slowness we are seeing are in: 1. Archive: Was ~50 mins on v11, is 1h 6m on v12 2. Left Joins: Randomly, so not with all queries, the slowness is significant: from seconds to 10s of minutes. So the Questions are: 1. What are the general guidelines on running Update Statistics- on databases, tables, indexes. Should we drop distributions? 2. Is there any config. parameter tweak that can impact Left JOINs. I noticed an APAR IT12047 that supports my finding but since we just upgraded it would take some months in development before we upgrade again in production. Is there a workaround for now. Thank you

Running the full suite of update statistics commands is advised after a migration, especially if you are experiencing performance problems. We aren't 100% sure if you need to drop distributions. Fernando suggests that it shouldn't be necessary, Art says he's seen performance issues when not dropping distributions after a migration, the Migration Guide says to drop them and I don't see why it would make a difference. Either way you're going to want to run update statistics and see if that helps. Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MURALI PAZHAYANNUR Sent: Wednesday, November 02, 2016 10:09 AM To: ids@iiug.org Subject: Performance on Informix 12.10.FC6 [38064] Last weekend we upgraded our Primary and Secondary HDR servers from 11.70.FC7 to 12.10.FC6. There have been a couple of performance drops noticed so for and am looking for some general guidance here. The config. param values we have in version 11 have been retained in v12. The slowness we are seeing are in: 1. Archive: Was ~50 mins on v11, is 1h 6m on v12 2. Left Joins: Randomly, so not with all queries, the slowness is significant: from seconds to 10s of minutes. So the Questions are: 1. What are the general guidelines on running Update Statistics- on databases, tables, indexes. Should we drop distributions? 2. Is there any config. parameter tweak that can impact Left JOINs. I noticed an APAR IT12047 that supports my finding but since we just upgraded it would take some months in development before we upgrade again in production. Is there a workaround for now. Thank you **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Whenever you upgrade it is Best Practice to drop distributions for all tables/databases and recreate the recommended levels of distributions from scratch. You do this in a single operation if you are using dostats: dostats -d <database|database spec|'*'> --drop-distributions --force-run Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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 Wed, Nov 2, 2016 at 11:08 AM, MURALI PAZHAYANNUR < pmurali@ftportfolios.com> wrote: > Last weekend we upgraded our Primary and Secondary HDR servers from > 11.70.FC7 > to 12.10.FC6. There have been a couple of performance drops noticed so for > and > am looking for some general guidance here. The config. param values we > have in > version 11 have been retained in v12. The slowness we are seeing are in: > > 1. Archive: Was ~50 mins on v11, is 1h 6m on v12 > 2. Left Joins: Randomly, so not with all queries, the slowness is > significant: > from seconds to 10s of minutes. > > So the Questions are: > > 1. What are the general guidelines on running Update Statistics- on > databases, > tables, indexes. Should we drop distributions? > > 2. Is there any config. parameter tweak that can impact Left JOINs. I > noticed > an APAR IT12047 that supports my finding but since we just upgraded it > would > take some months in development before we upgrade again in production. Is > there a workaround for now. > > Thank you > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114a934a4530ef0540530c7b

Andrew: Not saying I didn't, but when did I state it shouldn't be necessary? Just want to check what I may have said, as I could find arguments both ways probably.... For the OP: - I have no idea of what could be causing impact on backup performance... - For the other problem I'd suggest open a PMR. The APAR you mention may have a workaround, but from the description the workaround would avoid certain things in the new query plan (make it like the old one). But I'm confused because another note shows the $ONCONFIG parameters used and I'd say with those parameters the "bad" query plan should never happen. And it also shows some parameters that are not the default for V12. So, in short, I'd say this requires closer analisys and that's better done within a PMR. Regards. On Wed, Nov 2, 2016 at 3:23 PM, Andrew Ford <andrew@informix-dba.com> wrote: > Running the full suite of update statistics commands is advised after a > migration, especially if you are experiencing performance problems. > > We aren't 100% sure if you need to drop distributions. > > Fernando suggests that it shouldn't be necessary, Art says he's seen > performance issues when not dropping distributions after a migration, the > Migration Guide says to drop them and I don't see why it would make a > difference. > > Either way you're going to want to run update statistics and see if that > helps. > > Andrew > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > MURALI > PAZHAYANNUR > Sent: Wednesday, November 02, 2016 10:09 AM > To: ids@iiug.org > Subject: Performance on Informix 12.10.FC6 [38064] > > Last weekend we upgraded our Primary and Secondary HDR servers from > 11.70.FC7 to 12.10.FC6. There have been a couple of performance drops > noticed so for and am looking for some general guidance here. The config. > param values we have in version 11 have been retained in v12. The slowness > we are seeing are in: > > 1. Archive: Was ~50 mins on v11, is 1h 6m on v12 2. Left Joins: Randomly, > so > not with all queries, the slowness is significant: > from seconds to 10s of minutes. > > So the Questions are: > > 1. What are the general guidelines on running Update Statistics- on > databases, tables, indexes. Should we drop distributions? > > 2. Is there any config. parameter tweak that can impact Left JOINs. I > noticed an APAR IT12047 that supports my finding but since we just upgraded > it would take some months in development before we upgrade again in > production. Is there a workaround for now. > > Thank you > > ************************************************************ > **************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a114abc807a739305405331b4

Andrew: I reviewed the thread where this was discussed... And I think there were two discussion levels: 1- I argued that the engine should not require new statistics to be created (in most cases at least) and I opened an RFE for this which was refused 2- We then discussed if it would be needed to DROP DISTRUBUTIONS+ create new ones vs simply creating new ones. Art correclty pointed out that the manual states we should DROP them. I'd say that in most cases these would be equivalent, but I can imagine a few situations where this may not be so.... So my final statement about this would be "follow the manual".... or Art (which is more or less the same if not better ;) ) There may be old customized distributions which could not be replaced by new ones. There may be new defaults (won't happen frequently) that could lead to differences also.... So, why risk it? Regards. On Wed, Nov 2, 2016 at 3:35 PM, Fernando Nunes <domusonline@gmail.com> wrote: > Andrew: Not saying I didn't, but when did I state it shouldn't be > necessary? Just want to check what I may have said, as I could find > arguments both ways probably.... > > For the OP: > - I have no idea of what could be causing impact on backup performance... > - For the other problem I'd suggest open a PMR. The APAR you mention may > have a workaround, but from the description the workaround would avoid > certain things in the new query plan (make it like the old one). But I'm > confused because another note shows the $ONCONFIG parameters used and I'd > say with those parameters the "bad" query plan should never happen. And it > also shows some parameters that are not the default for V12. > > So, in short, I'd say this requires closer analisys and that's better done > within a PMR. > > Regards. > > On Wed, Nov 2, 2016 at 3:23 PM, Andrew Ford <andrew@informix-dba.com> > wrote: > > > Running the full suite of update statistics commands is advised after a > > migration, especially if you are experiencing performance problems. > > > > We aren't 100% sure if you need to drop distributions. > > > > Fernando suggests that it shouldn't be necessary, Art says he's seen > > performance issues when not dropping distributions after a migration, the > > Migration Guide says to drop them and I don't see why it would make a > > difference. > > > > Either way you're going to want to run update statistics and see if that > > helps. > > > > Andrew > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > MURALI > > PAZHAYANNUR > > Sent: Wednesday, November 02, 2016 10:09 AM > > To: ids@iiug.org > > Subject: Performance on Informix 12.10.FC6 [38064] > > > > Last weekend we upgraded our Primary and Secondary HDR servers from > > 11.70.FC7 to 12.10.FC6. There have been a couple of performance drops > > noticed so for and am looking for some general guidance here. The config. > > param values we have in version 11 have been retained in v12. The > slowness > > we are seeing are in: > > > > 1. Archive: Was ~50 mins on v11, is 1h 6m on v12 2. Left Joins: Randomly, > > so > > not with all queries, the slowness is significant: > > from seconds to 10s of minutes. > > > > So the Questions are: > > > > 1. What are the general guidelines on running Update Statistics- on > > databases, tables, indexes. Should we drop distributions? > > > > 2. Is there any config. parameter tweak that can impact Left JOINs. I > > noticed an APAR IT12047 that supports my finding but since we just > upgraded > > it would take some months in development before we upgrade again in > > production. Is there a workaround for now. > > > > Thank you > > > > ************************************************************ > > **************** > > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a114abc807a739305405331b4 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1134ed88103609054053652a

Back in May of this year when I was asking about dropping distributions vs. just rebuilding distributions after a migration you said, As far as I understand "DROP DISTRIBUTIONS" is only useful if you really need to test a query without distributions. .... WITH DISTIBUTIONS ONLY should replace existing ones. I took that to mean you thought the suggestion in the guide to drop distributions/rebuild distributions after a migration was pointless and you shouldn't have to do the explicit drop distributions and only have to rebuild distributions via update statistics. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Wednesday, November 02, 2016 10:36 AM To: ids@iiug.org Subject: Re: Performance on Informix 12.10.FC6 [38067] Andrew: Not saying I didn't, but when did I state it shouldn't be necessary? Just want to check what I may have said, as I could find arguments both ways probably.... For the OP: - I have no idea of what could be causing impact on backup performance... - For the other problem I'd suggest open a PMR. The APAR you mention may have a workaround, but from the description the workaround would avoid certain things in the new query plan (make it like the old one). But I'm confused because another note shows the $ONCONFIG parameters used and I'd say with those parameters the "bad" query plan should never happen. And it also shows some parameters that are not the default for V12. So, in short, I'd say this requires closer analisys and that's better done within a PMR. Regards. On Wed, Nov 2, 2016 at 3:23 PM, Andrew Ford <andrew@informix-dba.com> wrote: > Running the full suite of update statistics commands is advised after > a migration, especially if you are experiencing performance problems. > > We aren't 100% sure if you need to drop distributions. > > Fernando suggests that it shouldn't be necessary, Art says he's seen > performance issues when not dropping distributions after a migration, > the Migration Guide says to drop them and I don't see why it would > make a difference. > > Either way you're going to want to run update statistics and see if > that helps. > > Andrew > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > MURALI PAZHAYANNUR > Sent: Wednesday, November 02, 2016 10:09 AM > To: ids@iiug.org > Subject: Performance on Informix 12.10.FC6 [38064] > > Last weekend we upgraded our Primary and Secondary HDR servers from > 11.70.FC7 to 12.10.FC6. There have been a couple of performance drops > noticed so for and am looking for some general guidance here. The config. > param values we have in version 11 have been retained in v12. The > slowness we are seeing are in: > > 1. Archive: Was ~50 mins on v11, is 1h 6m on v12 2. Left Joins: > Randomly, so not with all queries, the slowness is significant: > from seconds to 10s of minutes. > > So the Questions are: > > 1. What are the general guidelines on running Update Statistics- on > databases, tables, indexes. Should we drop distributions? > > 2. Is there any config. parameter tweak that can impact Left JOINs. I > noticed an APAR IT12047 that supports my finding but since we just > upgraded it would take some months in development before we upgrade > again in production. Is there a workaround for now. > > Thank you > > ************************************************************ > **************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a114abc807a739305405331b4 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

That's what I intended to say. But I'm backing off... I'd recommend the safer/conservative path.... although I don't quite understand if there would really be a difference, I think it's safer to follow the guide. Sorry for changing my point... but politicians do it everyday ;) On Wed, Nov 2, 2016 at 3:51 PM, Andrew Ford <andrew@informix-dba.com> wrote: > Back in May of this year when I was asking about dropping distributions vs. > just rebuilding distributions after a migration you said, > > As far as I understand "DROP DISTRIBUTIONS" is only useful if you really > need to test a query without distributions. > ..... WITH DISTIBUTIONS ONLY should replace existing ones. > > I took that to mean you thought the suggestion in the guide to drop > distributions/rebuild distributions after a migration was pointless and you > shouldn't have to do the explicit drop distributions and only have to > rebuild distributions via update statistics. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando Nunes > Sent: Wednesday, November 02, 2016 10:36 AM > To: ids@iiug.org > Subject: Re: Performance on Informix 12.10.FC6 [38067] > > Andrew: Not saying I didn't, but when did I state it shouldn't be > necessary? > Just want to check what I may have said, as I could find arguments both > ways > probably.... > > For the OP: > - I have no idea of what could be causing impact on backup performance... > - For the other problem I'd suggest open a PMR. The APAR you mention may > have a workaround, but from the description the workaround would avoid > certain things in the new query plan (make it like the old one). But I'm > confused because another note shows the $ONCONFIG parameters used and I'd > say with those parameters the "bad" query plan should never happen. And it > also shows some parameters that are not the default for V12. > > So, in short, I'd say this requires closer analisys and that's better done > within a PMR. > > Regards. > > On Wed, Nov 2, 2016 at 3:23 PM, Andrew Ford <andrew@informix-dba.com> > wrote: > > > Running the full suite of update statistics commands is advised after > > a migration, especially if you are experiencing performance problems. > > > > We aren't 100% sure if you need to drop distributions. > > > > Fernando suggests that it shouldn't be necessary, Art says he's seen > > performance issues when not dropping distributions after a migration, > > the Migration Guide says to drop them and I don't see why it would > > make a difference. > > > > Either way you're going to want to run update statistics and see if > > that helps. > > > > Andrew > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > MURALI PAZHAYANNUR > > Sent: Wednesday, November 02, 2016 10:09 AM > > To: ids@iiug.org > > Subject: Performance on Informix 12.10.FC6 [38064] > > > > Last weekend we upgraded our Primary and Secondary HDR servers from > > 11.70.FC7 to 12.10.FC6. There have been a couple of performance drops > > noticed so for and am looking for some general guidance here. The config. > > param values we have in version 11 have been retained in v12. The > > slowness we are seeing are in: > > > > 1. Archive: Was ~50 mins on v11, is 1h 6m on v12 2. Left Joins: > > Randomly, so not with all queries, the slowness is significant: > > from seconds to 10s of minutes. > > > > So the Questions are: > > > > 1. What are the general guidelines on running Update Statistics- on > > databases, tables, indexes. Should we drop distributions? > > > > 2. Is there any config. parameter tweak that can impact Left JOINs. I > > noticed an APAR IT12047 that supports my finding but since we just > > upgraded it would take some months in development before we upgrade > > again in production. Is there a workaround for now. > > > > Thank you > > > > ************************************************************ > > **************** > > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a114abc807a739305405331b4 > > ************************************************************ > **************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1143f89a2cfcc7054053a6eb

My only argument for taking the risk is when you explicitly drop distributions you are running without any distributions until new ones can be built. For large tables that take a while to run this could cause performance issues because of sub optimal query plans. If we knew that old distributions were always replaced by new distributions then the engine can continue to use the old distributions while new ones are being created and avoid running without any distributions. This might be a moot point, I'm not sure. If a new engine can't use old distributions because the internal format has changed then the old distributions aren't doing you any good any way. If this is the case I was suggesting we drop distributions and then do a quick "update statistics medium distributions only" on all columns we want to have distributions for followed by the real "update statistics high distributions only" to minimize the time running without any distributions at all. Just put optimizer directives in all of your SQL statements and this problem goes away and you never have to run update statistics again. /sarcasm Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Wednesday, November 02, 2016 10:50 AM To: ids@iiug.org Subject: Re: Performance on Informix 12.10.FC6 [38068] Andrew: I reviewed the thread where this was discussed... And I think there were two discussion levels: 1- I argued that the engine should not require new statistics to be created (in most cases at least) and I opened an RFE for this which was refused 2- We then discussed if it would be needed to DROP DISTRUBUTIONS+ create new ones vs simply creating new ones. Art correclty pointed out that the manual states we should DROP them. I'd say that in most cases these would be equivalent, but I can imagine a few situations where this may not be so.... So my final statement about this would be "follow the manual".... or Art (which is more or less the same if not better ;) ) There may be old customized distributions which could not be replaced by new ones. There may be new defaults (won't happen frequently) that could lead to differences also.... So, why risk it? Regards. On Wed, Nov 2, 2016 at 3:35 PM, Fernando Nunes <domusonline@gmail.com> wrote: > Andrew: Not saying I didn't, but when did I state it shouldn't be > necessary? Just want to check what I may have said, as I could find > arguments both ways probably.... > > For the OP: > - I have no idea of what could be causing impact on backup performance... > - For the other problem I'd suggest open a PMR. The APAR you mention > may have a workaround, but from the description the workaround would > avoid certain things in the new query plan (make it like the old one). > But I'm confused because another note shows the $ONCONFIG parameters > used and I'd say with those parameters the "bad" query plan should > never happen. And it also shows some parameters that are not the default for V12. > > So, in short, I'd say this requires closer analisys and that's better > done within a PMR. > > Regards. > > On Wed, Nov 2, 2016 at 3:23 PM, Andrew Ford <andrew@informix-dba.com> > wrote: > > > Running the full suite of update statistics commands is advised > > after a migration, especially if you are experiencing performance problems. > > > > We aren't 100% sure if you need to drop distributions. > > > > Fernando suggests that it shouldn't be necessary, Art says he's seen > > performance issues when not dropping distributions after a > > migration, the Migration Guide says to drop them and I don't see why > > it would make a difference. > > > > Either way you're going to want to run update statistics and see if > > that helps. > > > > Andrew > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > > Of MURALI PAZHAYANNUR > > Sent: Wednesday, November 02, 2016 10:09 AM > > To: ids@iiug.org > > Subject: Performance on Informix 12.10.FC6 [38064] > > > > Last weekend we upgraded our Primary and Secondary HDR servers from > > 11.70.FC7 to 12.10.FC6. There have been a couple of performance > > drops noticed so for and am looking for some general guidance here. The config. > > param values we have in version 11 have been retained in v12. The > slowness > > we are seeing are in: > > > > 1. Archive: Was ~50 mins on v11, is 1h 6m on v12 2. Left Joins: > > Randomly, so not with all queries, the slowness is significant: > > from seconds to 10s of minutes. > > > > So the Questions are: > > > > 1. What are the general guidelines on running Update Statistics- on > > databases, tables, indexes. Should we drop distributions? > > > > 2. Is there any config. parameter tweak that can impact Left JOINs. > > I noticed an APAR IT12047 that supports my finding but since we just > upgraded > > it would take some months in development before we upgrade again in > > production. Is there a workaround for now. > > > > Thank you > > > > ************************************************************ > > **************** > > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a114abc807a739305405331b4 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1134ed88103609054053652a **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Very good point. Note that sometimes the "do as I say, not as I do" may apply. Mos customers won't run "onchecks" during the upgrade... although it's a documented step. But it simply takes too long.... many just run them on a restore image one or two days before the actual migration. Naturally every time we do something not exactly as documented we assume a certain risk. But we can't expect the software vendor to assume that risk for us.... And now I really feel like a politician.... Must verify my paycheck to see if it improved... On Wed, Nov 2, 2016 at 4:18 PM, Andrew Ford <andrew@informix-dba.com> wrote: > My only argument for taking the risk is when you explicitly drop > distributions you are running without any distributions until new ones can > be built. > > For large tables that take a while to run this could cause performance > issues because of sub optimal query plans. > > If we knew that old distributions were always replaced by new distributions > then the engine can continue to use the old distributions while new ones > are > being created and avoid running without any distributions. > > This might be a moot point, I'm not sure. If a new engine can't use old > distributions because the internal format has changed then the old > distributions aren't doing you any good any way. > > If this is the case I was suggesting we drop distributions and then do a > quick "update statistics medium distributions only" on all columns we want > to have distributions for followed by the real "update statistics high > distributions only" to minimize the time running without any distributions > at all. > > Just put optimizer directives in all of your SQL statements and this > problem > goes away and you never have to run update statistics again. /sarcasm > > Andrew > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando Nunes > Sent: Wednesday, November 02, 2016 10:50 AM > To: ids@iiug.org > Subject: Re: Performance on Informix 12.10.FC6 [38068] > > Andrew: I reviewed the thread where this was discussed... And I think there > were two discussion levels: > > 1- I argued that the engine should not require new statistics to be created > (in most cases at least) and I opened an RFE for this which was refused > 2- We then discussed if it would be needed to DROP DISTRUBUTIONS+ create > new > ones vs simply creating new ones. > Art correclty pointed out that the manual states we should DROP them. I'd > say that in most cases these would be equivalent, but I can imagine a few > situations where this may not be so.... So my final statement about this > would be "follow the manual".... or Art (which is more or less the same if > not better ;) ) > > There may be old customized distributions which could not be replaced by > new > ones. > There may be new defaults (won't happen frequently) that could lead to > differences also.... > > So, why risk it? > > Regards. > > On Wed, Nov 2, 2016 at 3:35 PM, Fernando Nunes <domusonline@gmail.com> > wrote: > > > Andrew: Not saying I didn't, but when did I state it shouldn't be > > necessary? Just want to check what I may have said, as I could find > > arguments both ways probably.... > > > > For the OP: > > - I have no idea of what could be causing impact on backup performance... > > - For the other problem I'd suggest open a PMR. The APAR you mention > > may have a workaround, but from the description the workaround would > > avoid certain things in the new query plan (make it like the old one). > > But I'm confused because another note shows the $ONCONFIG parameters > > used and I'd say with those parameters the "bad" query plan should > > never happen. And it also shows some parameters that are not the default > for V12. > > > > So, in short, I'd say this requires closer analisys and that's better > > done within a PMR. > > > > Regards. > > > > On Wed, Nov 2, 2016 at 3:23 PM, Andrew Ford <andrew@informix-dba.com> > > wrote: > > > > > Running the full suite of update statistics commands is advised > > > after a migration, especially if you are experiencing performance > problems. > > > > > > We aren't 100% sure if you need to drop distributions. > > > > > > Fernando suggests that it shouldn't be necessary, Art says he's seen > > > performance issues when not dropping distributions after a > > > migration, the Migration Guide says to drop them and I don't see why > > > it would make a difference. > > > > > > Either way you're going to want to run update statistics and see if > > > that helps. > > > > > > Andrew > > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > > > Of MURALI PAZHAYANNUR > > > Sent: Wednesday, November 02, 2016 10:09 AM > > > To: ids@iiug.org > > > Subject: Performance on Informix 12.10.FC6 [38064] > > > > > > Last weekend we upgraded our Primary and Secondary HDR servers from > > > 11.70.FC7 to 12.10.FC6. There have been a couple of performance > > > drops noticed so for and am looking for some general guidance here. The > config. > > > param values we have in version 11 have been retained in v12. The > > slowness > > > we are seeing are in: > > > > > > 1. Archive: Was ~50 mins on v11, is 1h 6m on v12 2. Left Joins: > > > Randomly, so not with all queries, the slowness is significant: > > > from seconds to 10s of minutes. > > > > > > So the Questions are: > > > > > > 1. What are the general guidelines on running Update Statistics- on > > > databases, tables, indexes. Should we drop distributions? > > > > > > 2. Is there any config. parameter tweak that can impact Left JOINs. > > > I noticed an APAR IT12047 that supports my finding but since we just > > upgraded > > > it would take some months in development before we upgrade again in > > > production. Is there a workaround for now. > > > > > > Thank you > > > > > > ************************************************************ > > > **************** > > > *** > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > ************************************************************ > > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > --001a114abc807a739305405331b4 > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a1134ed88103609054053652a > > ************************************************************ > **************** > *** > Forum Note: Use @@DQ@

Following the APAR IT12047, I downloaded 12.10.FC7W1 and the *WE versions to test if this (the occasional LEFT JOIN issue) was indeed fixed. And am running update statistics on it. Meanwhile, testing with SET OPTIMIZATION LOW on12.10.FC6 enables the slow query to run quickly. Not sure if this will be our workaround for all "LEFT JOIN' slow queries. I want to bring up a problem I had with today's forum posts. In spite of checking the box requesting all responses to come my email not all your responses (and replies to responses) got to me. For example, today I received just 2- the first from Andrew Ford and then from Art.

Murali: We are aware that there are a few issues with some forum posts not being sent out to the email forwarding users (myself included). This is most likely happening because the IIUG is in the process of migrating all of the web content and IIUG services to a new server. Currently nearly all content is now running on the new server, but there are two primary items still running on the old server. One is email services and the other are the forums. We are almost finished moving the remaining content and services to the new server where we expect that all will be well. Please bear with us for a little longer. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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 Wed, Nov 2, 2016 at 5:10 PM, MURALI PAZHAYANNUR <pmurali@ftportfolios.com > wrote: > Following the APAR IT12047, I downloaded 12.10.FC7W1 and the *WE versions > to > test if this (the occasional LEFT JOIN issue) was indeed fixed. And am > running > update statistics on it. Meanwhile, testing with SET OPTIMIZATION LOW on> 12.10.FC6 enables the slow query to run quickly. Not sure if this will be > our > workaround for all "LEFT JOIN' slow queries. > > I want to bring up a problem I had with today's forum posts. In spite of > checking the box requesting all responses to come my email not all your > responses (and replies to responses) got to me. For example, today I > received > just 2- the first from Andrew Ford and then from Art. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1144513c54e9f10540584d00