why update statistics after IDS upgrade ?
Q: is UPDATE STATISTICS really needed after upgrading IDS (10.00 to 11.50), given data hasn't changed and a full run on 10+ TB would take days? Replies: the optimizer and the stored statistics format change between versions, so old distributions can mislead or confuse the optimizer (e.g. nrows becomes FLOAT in 11.50); Art Kagel advised dropping old distributions, doing a full dostats-style run and recompiling SPL routines. Others noted the Migration Guide calls table stats "optional/if you have performance problems" but requires UPDATE STATISTICS on the system catalog tables (tabid < 100) in every database, plus procedures — quick even on huge instances; a medium run on tables first, then normal stats in production, was suggested. No single definitive answer, but consensus is sysmaster alone isn't enough.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, there exists the recommendation to do an update statistics after an IDS upgrade. Why ? The data in the instance didn't change. An update statistics for all databases of an instance might be no problem when running instances with a few GB, but we have instances with over 10 TB - an update statistics on all tables will run for days (especially the low-option needs a lot of time). Is the update statistics really necessary ? Is it enough to run update statistics on the sysmaster database only ? At the moment the most interesting upgrade path to us is from IDS10FC7 to IDS11.50FC5 (or FC6). Regards, Andreas Kutsche ------------------------------------------- SPAR Osterreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 84923 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschutzte Informationen, insbesondere Betriebs- oder Geschaftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfanger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschlie?lich fur den Adressaten bestimmt. Sollten Sie die E-Mail irrtumlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu loschen und sich mit uns in Verbindung zu setzen. Uber das Internet versandte E-Mails konnen leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schlie?en wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich bestatigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht fur evtl. hieraus entstehende Schaden. Wir danken fur Ihr Verstandnis. Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us. E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. -------------------------------------------
Andreas.KUTSCHE@spar.at wrote: > Hi, > > there exists the recommendation to do an update statistics after an IDS > upgrade. > > Why ? The data in the instance didn't change. But the optimiser might be different, so the old statistics might be useless or even worse, downright misleading. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
2010/1/19 Obnoxio The Clown <obnoxio@serendipita.com>: > Andreas.KUTSCHE@spar.at wrote: >> Hi, >> >> there exists the recommendation to do an update statistics after an IDS >> upgrade. >> >> Why ? The data in the instance didn't change. > > But the optimiser might be different, so the old statistics might be > useless or even worse, downright misleading. > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > I will now proceed to pleasure myself with this fish. > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Also I believe the upgrade procedure actually removes existing statistics, certainly it did on upgades from 7 to 9 and 9 to 10. Keith
Andreas We had this discussion before, I asked nearly the same question few month ago. My experience is and the recommandations in the release note, resp. Migration Guide are (IIRC): Update Statistics for all procedures, update statistics medium for tables. It's really fast. Then do your Update Statistics as you are used to while production is running again. HTH, Reinhard. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of > Keith Simmons > Sent: Tuesday, January 19, 2010 10:47 AM > To: ids@iiug.org > Subject: Re: why update statistics after IDS upgrade ? [18708] > > > 2010/1/19 Obnoxio The Clown <obnoxio@serendipita.com>: > > Andreas.KUTSCHE@spar.at wrote: > >> Hi, > >> > >> there exists the recommendation to do an update statistics > after an IDS > >> upgrade. > >> > >> Why ? The data in the instance didn't change. > > > > But the optimiser might be different, so the old statistics > might be > > useless or even worse, downright misleading. > > > > -- > > Cheers, > > Obnoxio The Clown > > > > http://obotheclown.blogspot.com > > I will now proceed to pleasure myself with this fish. > > > > -- > > This message has been scanned for viruses and > > dangerous content by > OpenProtect(http://www.openprotect.com), and is > > believed to be clean. > > > > > > > ************************************************************** > ***************** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > Also I believe the upgrade procedure actually removes existing > statistics, certainly it did on upgades from 7 to 9 and 9 to 10. > > Keith > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. >
Hi,
the statistics are not deleted during the upgrade from IDS10 to IDS11.50.
LOW: the relevant fields in systables,sysindices,syscolumns are filled
MED/HIGH: dbschema -d <database> -hd <table> shows distributions for the
tables (with construction date before upgrade) - they look reasonable .
The statistic values (min/max values, number of rows/pages, distributions)
seem to be independent from the optimizer.
Maybe the new release will collect additional information which will be
missing after the upgrade (and mislead the optimizer).
Does anybody know about new statistic values from IDS10 to IDS11.50 ?
Regards,
Andreas Kutsche
>
-------------------------------------------
SPAR Österreichische Warenhandels-AG
Hauptzentrale
A - 5015 Salzburg, Europastrasse 3
FN 34170 a
Tel: +43 662 4470 84923
Mobile: +43 664 6259575
E-Mail: Andreas.KUTSCHE@spar.at
Internet: http://www.spar.at
Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich
geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse,
enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die
Informationen in dieser E-Mail sind ausschließlich für den Adressaten
bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir
Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung
zu setzen.
Über das Internet versandte E-Mails können leicht manipuliert oder unter
fremdem Namen erstellt werden. Daher schließen wir die rechtliche
Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der
Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich
bestätigt und gezeichnet wird.
Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung
von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl.
hieraus entstehende Schäden.
Wir danken für Ihr Verständnis.
Important notice: The contents of this e-mail may contain confidential and
legally protected information that is in particular related to operational and
trade secrets, which the recipient is obliged to treat as confidential. The
information in this e-mail is made available exclusively for use by the
addressee. In the event that the e-mail may have been sent to you in error, we
would ask you to kindly delete this communication from your system and to
contact us.
E-mails sent via the Internet can be easily manipulated or sent out under
someone else's name. We therefore do not accept legal liability for the
information contained in this communication. The contents of the e-mail are
only legally binding if they have been confirmed and signed by us in writing.
If, in spite of our using Antivirus protection software, a virus may have
penetrated your system through the sending of this e-mail, we do not accept
liability for any damage that may possibly arise as a result of this.
We trust that you appreciate our position.
-------------------------------------------
-----Ursprüngliche Nachricht-----
> Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von
> Keith Simmons
> Gesendet: Dienstag, 19. Januar 2010 10:47
> An: ids@iiug.org
> Betreff: Re: why update statistics after IDS upgrade ? [18708]
>
> 2010/1/19 Obnoxio The Clown <obnoxio@serendipita.com>:
> > Andreas.KUTSCHE@spar.at wrote:
> >> Hi,
> >>
> >> there exists the recommendation to do an update statistics after an IDS
> >> upgrade.
> >>
> >> Why ? The data in the instance didn't change.
> >
> > But the optimiser might be different, so the old statistics might be
> > useless or even worse, downright misleading.
> >
> > --
> > Cheers,
> > Obnoxio The Clown
> >
> > http://obotheclown.blogspot.com
> > I will now proceed to pleasure myself with this fish.
> >
> > --
> > This message has been scanned for viruses and
> > dangerous content by OpenProtect(http://www.openprotect.com), and is
> > believed to be clean.
> >
> >
> >
> **************************************************************************
> *****
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> Also I believe the upgrade procedure actually removes existing
> statistics, certainly it did on upgades from 7 to 9 and 9 to 10.
>
> Keith
>
>
> **************************************************************************
> *****
> Forum Note: Use "Reply" to post a response in the discussion forum.
You have to run update stats after an upgrade using your normal methods except that it MAY be important to drop all distributions before you do that. There are changes to the stats that are kept in the encoded data structure from one version to another and if you don't drop the older distributions and create new clean ones the optimizer can become very confused. You may be OK between minor versions but I would never trust to that. Also all SPL routines must be recompiled after replacing the data distributions. So, a full dostats run or the equivalent in every database is a VERY good idea! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, 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 Tue, Jan 19, 2010 at 4:23 AM, Andreas.KUTSCHE@spar.at < andreas.kutsche@spar.at> wrote: > Hi, > > there exists the recommendation to do an update statistics after an IDS > upgrade. > > Why ? The data in the instance didn't change. > > An update statistics for all databases of an instance might be no > problem when running instances with a few GB, but we have instances with > over 10 TB - an update statistics on all tables will run for days > (especially the low-option needs a lot of time). > > Is the update statistics really necessary ? > > Is it enough to run update statistics on the sysmaster database only ? > > At the moment the most interesting upgrade path to us is from IDS10FC7 > to IDS11.50FC5 (or FC6). > > Regards, > Andreas Kutsche > ------------------------------------------- > SPAR Osterreichische Warenhandels-AG > Hauptzentrale > A - 5015 Salzburg, Europastrasse 3 > FN 34170 a > > Tel: +43 662 4470 84923 > Mobile: +43 664 6259575 > E-Mail: Andreas.KUTSCHE@spar.at > Internet: http://www.spar.at > > Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich > geschutzte Informationen, insbesondere Betriebs- oder Geschaftsgeheimnisse, > enthalten, zu deren Geheimhaltung der Empfanger verpflichtet ist. Die > Informationen in dieser E-Mail sind ausschlie?lich fur den Adressaten > bestimmt. Sollten Sie die E-Mail irrtumlich erhalten haben so ersuchen wir > Sie, die Nachricht von Ihrem System zu loschen und sich mit uns in > Verbindung > zu setzen. > Uber das Internet versandte E-Mails konnen leicht manipuliert oder unter > fremdem Namen erstellt werden. Daher schlie?en wir die rechtliche > Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der > Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich > bestatigt und gezeichnet wird. > Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die > Zusendung > von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht fur evtl. > hieraus entstehende Schaden. > Wir danken fur Ihr Verstandnis. > > Important notice: The contents of this e-mail may contain confidential and > legally protected information that is in particular related to operational > and > trade secrets, which the recipient is obliged to treat as confidential. The > information in this e-mail is made available exclusively for use by the > addressee. In the event that the e-mail may have been sent to you in error, > we > would ask you to kindly delete this communication from your system and to > contact us. > E-mails sent via the Internet can be easily manipulated or sent out under > someone else's name. We therefore do not accept legal liability for the > information contained in this communication. The contents of the e-mail are > only legally binding if they have been confirmed and signed by us in > writing. > If, in spite of our using Antivirus protection software, a virus may have > penetrated your system through the sending of this e-mail, we do not accept > liability for any damage that may possibly arise as a result of this. > We trust that you appreciate our position. > > ------------------------------------------- > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00163646dbdcbdf29c047d82eb1d
Andreas.KUTSCHE@spar.at schrieb: > Hi, > > there exists the recommendation to do an update statistics after an IDS > upgrade. > > Why ? The data in the instance didn't change. > > An update statistics for all databases of an instance might be no > problem when running instances with a few GB, but we have instances with > over 10 TB - an update statistics on all tables will run for days > (especially the low-option needs a lot of time). > > Is the update statistics really necessary ? > > Is it enough to run update statistics on the sysmaster database only ? > > At the moment the most interesting upgrade path to us is from IDS10FC7 > to IDS11.50FC5 (or FC6). > > Regards, > Andreas Kutsche > ------------------------------------------- Hi Andreas, one of the differences V10 vs. V11.50.FC5 is, that nrows has a new datatype, it is of type FLOAT now and therefore can be > 2**32 - 1. IMHO this makes it a necessity to run update statistics low, but I might be wrong. At least the (new) value of the system catalog table sysindices will be inserted by update statitics low. Also scripting or programmatic evaluation of nrows must be retested, as casting FLOAT to 32 bit INT means danger to lose information or a datacoonversion error might occur. dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe
Hi, everyone, I set the update statistics with OAT interface and looks fine to me. All the criteria used have very precision to execute e select the table/columns to work on. It's a 1.7 Tb size enviroment database. André Luiz > To: ids@iiug.org > From: richard.kofler@chello.at > Subject: Re: why update statistics after IDS upgrade ? [18713] > Date: Tue, 19 Jan 2010 07:49:52 -0500 > > Andreas.KUTSCHE@spar.at schrieb: > > Hi, > > > > there exists the recommendation to do an update statistics after an IDS > > upgrade. > > > > Why ? The data in the instance didn't change. > > > > An update statistics for all databases of an instance might be no > > problem when running instances with a few GB, but we have instances with > > over 10 TB - an update statistics on all tables will run for days > > (especially the low-option needs a lot of time). > > > > Is the update statistics really necessary ? > > > > Is it enough to run update statistics on the sysmaster database only ? > > > > At the moment the most interesting upgrade path to us is from IDS10FC7 > > to IDS11.50FC5 (or FC6). > > > > Regards, > > Andreas Kutsche > > ------------------------------------------- > > Hi Andreas, > > one of the differences V10 vs. V11.50.FC5 is, that > nrows has a new datatype, it is of type FLOAT now and > therefore can be > 2**32 - 1. > IMHO this makes it a necessity to run update statistics low, > but I might be wrong. At least the (new) value of the system catalog > table sysindices will be inserted by update statitics low. > > Also scripting or programmatic evaluation of nrows must be > retested, as casting FLOAT to 32 bit INT means danger to lose information > or a datacoonversion error might occur. > > dic_k > -- > Richard Kofler > SOLID STATE EDV > Dienstleistungen GmbH > Vienna/Austria/Europe > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Sabia que você tem 25Gb de armazenamento grátis na web? Conheça o Skydrive agora. http://www.windowslive.com.br/public/product.aspx/view/5?ocid=CRM-WindowsLive:pr odutoSkyDrive:Tagline:WLCRM:On:WL:pt-BR:SkyDrive
From the IDS11.5 Migration Guide pages 5-6 and 5-7 ............................. Completing Required Post-Migration Tasks ... 2. Optionally run UPDATE STATISTICS on your tables (not system catalog tables) and on UDRs that perform queries, if you have performance problems after migrating. For more information, see Optionally Update Statistics on Your Tables After Migrating on page 5-8. 3. Run UPDATE STATISTICS on some system catalog tables ............................. > Andreas Questions... > Is the update statistics really necessary ? Many experts will recommend all statistics be updated as a precaution. However, I note the manuals phrasing "optionally" and "if you have performance problems after migrating" when it comes to your tables. You can often run just fine without updating statistics on all tables. > Is it enough to run update statistics on the sysmaster database only ? No. You should also update statistics on the system catalog tables (tabid < 100) in every database. These tables do change during upgrades. Updating statistics on these key tables should be relatively quick even if you have multiple TB of data and tens of thousands of tables.