Fwd: HELP! Upgrade to V10

On 02/10/2007, TBP <TheBigPotatoe@nothere.co.uk> wrote: > On 2 Oct, 10:01, "Keith Simmons" <smile...@googlemail.com> wrote: > > On 02/10/2007, Neil Truby <neil.tr...@ardenta.com> wrote: > > > > > "Keith Simmons" <smile...@googlemail.com> wrote in message > > >news:mailman.173.1191312549.26742.informix-list@iiug.org... > > > > Was IDS7.31 FD6 on Solaris 5.8, upgraded to IDS 10 FC5. Followed the > > > > migration guide and have updated stats. Performance stinks. The > > > > database has many synonyms to a remote server, the link is LAN (100 > > > > Mbs), the migration guide didn't mention synonyms specifically, any > > > > view on whether it is worth dropping/recreating them?? > > > > Any other thoughts on this migration? I need help quickly!! > > > > > Update statistics?> > > > > _______________________________________________ > > > Informix-list mailing list > > > Informix-l...@iiug.org > > >http://www.iiug.org/mailman/listinfo/informix-list > > > > Neil > > > > Thanks, Statistics have been updated (two or three times!) > > > > Keith > > Have you performed "update statistics drop distributions" before > rebuilding the distributions? > > What is your update statistics strategy? (i.e. simple "update > statistics" to complex "do_stats")? > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > TBP Thanks for responding Yes, did a drop distrib before rebuild. Strategy is low on each table and medium for the lead column of each index. Pragmatism of old, slow disks and the limited conversion window vs update stats requirements of V10. I will instigate a more thorogh set of stats on a rolling basis. Our queries use some large temp tables and I understand there is a useful Environmental Variable (next bounce). We have narrowed some of our issues down to Stored Procedures. Dropping and recreating a couple has helped. Are there known issues with bringing SPs forward from 7 to 10? I updated stats on sysprocedures as per Migration Guide but nothing on sysprocplan, which has been ginving a 211:154 ISAM error occasionally. PMR with Marco. Keith

On Oct 2, 8:45 am, "Keith Simmons" <smile...@googlemail.com> wrote: > On 02/10/2007, TBP <TheBigPota...@nothere.co.uk> wrote: > > > On 2 Oct, 10:01, "Keith Simmons" <smile...@googlemail.com> wrote: > > > On 02/10/2007, Neil Truby <neil.tr...@ardenta.com> wrote: > > > > > "Keith Simmons" <smile...@googlemail.com> wrote in message > > > >news:mailman.173.1191312549.26742.informix-list@iiug.org... > > > > > Was IDS7.31 FD6 on Solaris 5.8, upgraded to IDS 10 FC5. Followed the > > > > > migration guide and have updated stats. Performance stinks. The > > > > > database has many synonyms to a remote server, the link is LAN (100 > > > > > Mbs), the migration guide didn't mention synonyms specifically, any > > > > > view on whether it is worth dropping/recreating them?? > > > > > Any other thoughts on this migration? I need help quickly!! > > > > > Update statistics?> > > > > _______________________________________________ > > > > Informix-list mailing list > > > > Informix-l...@iiug.org > > > >http://www.iiug.org/mailman/listinfo/informix-list > > > > Neil > > > > Thanks, Statistics have been updated (two or three times!) > > > > Keith > > > Have you performed "update statistics drop distributions" before > > rebuilding the distributions? > > > What is your update statistics strategy? (i.e. simple "update > > statistics" to complex "do_stats")? > > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list > > TBP > > Thanks for responding > Yes, did a drop distrib before rebuild. > Strategy is low on each table and medium for the lead column of each > index. Pragmatism of old, slow disks and the limited conversion window > vs update stats requirements of V10. I will instigate a more thorogh > set of stats on a rolling basis. Our queries use some large temp > tables and I understand there is a useful Environmental Variable (next bounce). > We have narrowed some of our issues down to Stored Procedures. > Dropping and recreating a couple has helped. Are there known issues > with bringing SPs forward from 7 to 10? I updated stats on > sysprocedures as per Migration Guide but nothing on sysprocplan, which > has been ginving a 211:154 ISAM error occasionally. > PMR with Marco. > > Keith Keith - In your SPL, do you use temp tables? If so, do you create them explicitly prior to the action (SQL) using them, or are they created "on the fly" (select .... into temp blah;)? If you use the first approach, the engine will do the normal optimization and create a query plan each time ... this is an 'unknown schema'. The second approach is preferred. Now - I realize that this choice would have also been true in 7.31 (as far as I know), but it's something to look for and to test. Haven't thought it through, but I wonder how differently the QP is created in 10 vs. 7.31. And yes, as someone stated, around IDS 10.x.y5 there were some significant bugs that were pointed out "loudly" by many on this group. Check to make sure you have the latest "safe release", or I would hope the PMR discussion(s) would reveal this answer. HTH - Mark

On Oct 2, 8:45 am, "Keith Simmons" <smile...@googlemail.com> wrote: > On 02/10/2007, TBP <TheBigPota...@nothere.co.uk> wrote: > > > On 2 Oct, 10:01, "Keith Simmons" <smile...@googlemail.com> wrote: > > > On 02/10/2007, Neil Truby <neil.tr...@ardenta.com> wrote: > > > > > "Keith Simmons" <smile...@googlemail.com> wrote in message > > > >news:mailman.173.1191312549.26742.informix-list@iiug.org... > > > > > Was IDS7.31 FD6 on Solaris 5.8, upgraded to IDS 10 FC5. Followed the > > > > > migration guide and have updated stats. Performance stinks. The > > > > > database has many synonyms to a remote server, the link is LAN (100 > > > > > Mbs), the migration guide didn't mention synonyms specifically, any > > > > > view on whether it is worth dropping/recreating them?? > > > > > Any other thoughts on this migration? I need help quickly!! > > > > > Update statistics?> > > > > _______________________________________________ > > > > Informix-list mailing list > > > > Informix-l...@iiug.org > > > >http://www.iiug.org/mailman/listinfo/informix-list > > > > Neil > > > > Thanks, Statistics have been updated (two or three times!) > > > > Keith > > > Have you performed "update statistics drop distributions" before > > rebuilding the distributions? > > > What is your update statistics strategy? (i.e. simple "update > > statistics" to complex "do_stats")? > > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list > > TBP > > Thanks for responding > Yes, did a drop distrib before rebuild. > Strategy is low on each table and medium for the lead column of each > index. Pragmatism of old, slow disks and the limited conversion window > vs update stats requirements of V10. I will instigate a more thorogh > set of stats on a rolling basis. Our queries use some large temp > tables and I understand there is a useful Environmental Variable (next bounce). > We have narrowed some of our issues down to Stored Procedures. > Dropping and recreating a couple has helped. Are there known issues > with bringing SPs forward from 7 to 10? I updated stats on > sysprocedures as per Migration Guide but nothing on sysprocplan, which > has been ginving a 211:154 ISAM error occasionally. > PMR with Marco. > > Keith Updating stats on sysprocedures is fine, but irrelevant to your problem. You have to recompile all of your stored procedures when you update statistics: UPDATE STATISTICS FOR PROCEDURE procname; Certainly a more aggressive update stats protocol. like that implemented by dostats, will help a bit, but I think you've found the major problem in the SPL. Every procedure prepares a query plan at creation/recompilation time in sysprocplan and that plan is out-of- date. Since the compiled query plan was created by 7 the IDS 10 optimizer may not be recognizing that the query plan is NG so it's not forcing a recompile on first execution. This is another detail that dostats takes care of for you, BTW. Art S. Kagel

On 02/10/2007, mark.scranton@gmail.com <mark.scranton@gmail.com> wrote: > On Oct 2, 8:45 am, "Keith Simmons" <smile...@googlemail.com> wrote: > > On 02/10/2007, TBP <TheBigPota...@nothere.co.uk> wrote: > > > > > On 2 Oct, 10:01, "Keith Simmons" <smile...@googlemail.com> wrote: > > > > On 02/10/2007, Neil Truby <neil.tr...@ardenta.com> wrote: > > > > > > > "Keith Simmons" <smile...@googlemail.com> wrote in message > > > > >news:mailman.173.1191312549.26742.informix-list@iiug.org... > > > > > > Was IDS7.31 FD6 on Solaris 5.8, upgraded to IDS 10 FC5. Followed the > > > > > > migration guide and have updated stats. Performance stinks. The > > > > > > database has many synonyms to a remote server, the link is LAN (100 > > > > > > Mbs), the migration guide didn't mention synonyms specifically, any > > > > > > view on whether it is worth dropping/recreating them?? > > > > > > Any other thoughts on this migration? I need help quickly!! > > > > > > > Update statistics?> > > > > > > _______________________________________________ > > > > > Informix-list mailing list > > > > > Informix-l...@iiug.org > > > > >http://www.iiug.org/mailman/listinfo/informix-list > > > > > > Neil > > > > > > Thanks, Statistics have been updated (two or three times!) > > > > > > Keith > > > > > Have you performed "update statistics drop distributions" before > > > rebuilding the distributions? > > > > > What is your update statistics strategy? (i.e. simple "update > > > statistics" to complex "do_stats")? > > > > > _______________________________________________ > > > Informix-list mailing list > > > Informix-l...@iiug.org > > >http://www.iiug.org/mailman/listinfo/informix-list > > > > TBP > > > > Thanks for responding > > Yes, did a drop distrib before rebuild. > > Strategy is low on each table and medium for the lead column of each > > index. Pragmatism of old, slow disks and the limited conversion window > > vs update stats requirements of V10. I will instigate a more thorogh > > set of stats on a rolling basis. Our queries use some large temp > > tables and I understand there is a useful Environmental Variable (next bounce). > > We have narrowed some of our issues down to Stored Procedures. > > Dropping and recreating a couple has helped. Are there known issues > > with bringing SPs forward from 7 to 10? I updated stats on > > sysprocedures as per Migration Guide but nothing on sysprocplan, which > > has been ginving a 211:154 ISAM error occasionally. > > PMR with Marco. > > > > Keith > > Keith - > > In your SPL, do you use temp tables? If so, do you create them > explicitly prior to the action (SQL) using them, or are they created > "on the fly" (select .... into temp blah;)? If you use the first > approach, the engine will do the normal optimization and create a > query plan each time ... this is an 'unknown schema'. The second > approach is preferred. Now - I realize that this choice would have > also been true in 7.31 (as far as I know), but it's something to look > for and to test. Haven't thought it through, but I wonder how > differently the QP is created in 10 vs. 7.31. And yes, as someone > stated, around IDS 10.x.y5 there were some significant bugs that were > pointed out "loudly" by many on this group. Check to make sure you > have the latest "safe release", or I would hope the PMR discussion(s) > would reveal this answer. > > HTH - > Mark > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > Mark Most are created "INTO TEMP". There is an undocumented env variable that sorts the QP out for these by maintaining stats. Bounced engine to include it and will monitor. Thanks keith

On 02/10/2007, Art S. Kagel <art.kagel@gmail.com> wrote: > On Oct 2, 8:45 am, "Keith Simmons" <smile...@googlemail.com> wrote: > > On 02/10/2007, TBP <TheBigPota...@nothere.co.uk> wrote: > > > > > On 2 Oct, 10:01, "Keith Simmons" <smile...@googlemail.com> wrote: > > > > On 02/10/2007, Neil Truby <neil.tr...@ardenta.com> wrote: > > > > > > > "Keith Simmons" <smile...@googlemail.com> wrote in message > > > > >news:mailman.173.1191312549.26742.informix-list@iiug.org... > > > > > > Was IDS7.31 FD6 on Solaris 5.8, upgraded to IDS 10 FC5. Followed the > > > > > > migration guide and have updated stats. Performance stinks. The > > > > > > database has many synonyms to a remote server, the link is LAN (100 > > > > > > Mbs), the migration guide didn't mention synonyms specifically, any > > > > > > view on whether it is worth dropping/recreating them?? > > > > > > Any other thoughts on this migration? I need help quickly!! > > > > > > > Update statistics?> > > > > > > _______________________________________________ > > > > > Informix-list mailing list > > > > > Informix-l...@iiug.org > > > > >http://www.iiug.org/mailman/listinfo/informix-list > > > > > > Neil > > > > > > Thanks, Statistics have been updated (two or three times!) > > > > > > Keith > > > > > Have you performed "update statistics drop distributions" before > > > rebuilding the distributions? > > > > > What is your update statistics strategy? (i.e. simple "update > > > statistics" to complex "do_stats")? > > > > > _______________________________________________ > > > Informix-list mailing list > > > Informix-l...@iiug.org > > >http://www.iiug.org/mailman/listinfo/informix-list > > > > TBP > > > > Thanks for responding > > Yes, did a drop distrib before rebuild. > > Strategy is low on each table and medium for the lead column of each > > index. Pragmatism of old, slow disks and the limited conversion window > > vs update stats requirements of V10. I will instigate a more thorogh > > set of stats on a rolling basis. Our queries use some large temp > > tables and I understand there is a useful Environmental Variable (next bounce). > > We have narrowed some of our issues down to Stored Procedures. > > Dropping and recreating a couple has helped. Are there known issues > > with bringing SPs forward from 7 to 10? I updated stats on > > sysprocedures as per Migration Guide but nothing on sysprocplan, which > > has been ginving a 211:154 ISAM error occasionally. > > PMR with Marco. > > > > Keith > > Updating stats on sysprocedures is fine, but irrelevant to your > problem. You have to recompile all of your stored procedures when you > update statistics: > UPDATE STATISTICS FOR PROCEDURE procname;> > Certainly a more aggressive update stats protocol. like that > implemented by dostats, will help a bit, but I think you've found the > major problem in the SPL. Every procedure prepares a query plan at > creation/recompilation time in sysprocplan and that plan is out-of- > date. Since the compiled query plan was created by 7 the IDS 10 > optimizer may not be recognizing that the query plan is NG so it's not > forcing a recompile on first execution. This is another detail that > dostats takes care of for you, BTW. > > Art S. Kagel > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > Art Thanks, am running some more aggressive stats tonight, plus a couple of small config changes, followed by update stats on the SPs. Things looking better aleady !! Keith

There is one critical step missing from a V7 to V9 (prob 10 as well) in that you should drop/recreate ALL stored procedures. It is not listed in the migration guide but UK Tech Support said it should be performed as SP's are handled differently. RegardsColinThere are 10 types of people in the world, those that understand binary and those that don't> From: art.kagel@gmail.com> Subject: Re: Fwd: HELP! Upgrade to V10> Date: Tue, 2 Oct 2007 16:54:18 +0000> To: informix-list@iiug.org> > On Oct 2, 8:45 am, "Keith Simmons" <smile...@googlemail.com> wrote:> > On 02/10/2007, TBP <TheBigPota...@nothere.co.uk> wrote:> >> > > On 2 Oct, 10:01, "Keith Simmons" <smile...@googlemail.com> wrote:> > > > On 02/10/2007, Neil Truby <neil.tr...@ardenta.com> wrote:> >> > > > > "Keith Simmons" <smile...@googlemail.com> wrote in message> > > > >news:mailman.173.1191312549.26742.informix-list@iiug.org...> > > > > > Was IDS7.31 FD6 on Solaris 5.8, upgraded to IDS 10 FC5. Followed the> > > > > > migration guide and have updated stats. Performance stinks. The> > > > > > database has many synonyms to a remote server, the link is LAN (100> > > > > > Mbs), the migration guide didn't mention synonyms specifically, any> > > > > > view on whether it is worth dropping/recreating them??> > > > > > Any other thoughts on this migration? I need help quickly!!> >> > > > > Update statistics?> >> > > > > _______________________________________________> > > > > Informix-list mailing list> > > > > Informix-l...@iiug.org> > > > >http://www.iiug.org/mailman/listinfo/informix-list>; >> > > > Neil> >> > > > Thanks, Statistics have been updated (two or three times!)> >> > > > Keith> >> > > Have you performed "update statistics drop distributions" before> > > rebuilding the distributions?> >> > > What is your update statistics strategy? (i.e. simple "update> > > statistics" to complex "do_stats")?> >> > > _______________________________________________> > > Informix-list mailing list> > > Informix-l...@iiug.org> > >http://www.iiug.org/mailman/listinfo/informix-list>; >> > TBP> >> > Thanks for responding> > Yes, did a drop distrib before rebuild.> > Strategy is low on each table and medium for the lead column of each> > index. Pragmatism of old, slow disks and the limited conversion window> > vs update stats requirements of V10. I will instigate a more thorogh> > set of stats on a rolling basis. Our queries use some large temp> > tables and I understand there is a useful Environmental Variable (next bounce).> > We have narrowed some of our issues down to Stored Procedures.> > Dropping and recreating a couple has helped. Are there known issues> > with bringing SPs forward from 7 to 10? I updated stats on> > sysprocedures as per Migration Guide but nothing on sysprocplan, which> > has been ginving a 211:154 ISAM error occasionally.> > PMR with Marco.> >> > Keith> > Updating stats on sysprocedures is fine, but irrelevant to your> problem. You have to recompile all of your stored procedures when you> update statistics:> UPDATE STATISTICS FOR PROCEDURE procname;> > Certainly a more aggressive update stats protocol. like that> implemented by dostats, will help a bit, but I think you've found the> major problem in the SPL. Every procedure prepares a query plan at> creation/recompilation time in sysprocplan and that plan is out-of-> date. Since the compiled query plan was created by 7 the IDS 10> optimizer may not be recognizing that the query plan is NG so it's not> forcing a recompile on first execution. This is another detail that> dostats takes care of for you, BTW.> > Art S. Kagel> > > _______________________________________________> Informix-list mailing list> Informix-list@iiug.org> http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Celeb spotting – Play CelebMashup and win cool prizes https://www.celebmashup.com