Re: Fwd: HELP! Upgrade to V10

On 02/10/2007, Keith Simmons <smiley73@googlemail.com> wrote: > 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 > ALL Many thanks to all my correspondants. Following a couple of config changes and some major stats updating I have recovered performance to that of before the upgrade. The cry is not UPDATE STATISTICS, but READ THE PERFORMANCE GUIDE and UPDATE STATISTICS FOR PROCEDURES !! Once again thanks Keith