Re: Mysterious update statistics

Habichtsberg, Reinhard wrote: > Hi all. > Hi. First to answer your first direct question. No it doesn't make any difference. The UPDATE STATISTICS commands do not operate ONLY on the changes to the table but on the entire table (or a sample of the table if MEDIUM level stats are requested). No, on to something different. There are several ways to speed up the generation of statistics, depending on what version of the engine you are running (it is always a good idea to quote your version and platform information when you post so those who reply can be specific). Have you read the Performance Guide and John Miller III's white paper on the subject? (http://www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/0203miller.html) Basically you do not run the database wide or even the table wide commands but run sets of columns as required. Also using PDQPRIORITY maximizing MGM memory available for sorting during the update statistics, running mulitple tables in parallel if you have the available CPU and memory resources, and minimizing the number of tables that are updated on any given day. These last two points I can help with: Do you use my dostats utility? If not get it and use it, many shops do. Dostats is contained in the package utils2_ak available for download from the IIUG Software Repository (www.iiug.org/software) or from the Oninit web site (www.oninit.com). The package also contains the script drive_dostats. Dostats implements the recommendations in the performance guide as modified by John's paper and its actual operation is dependent on the server version that it detects at runtime. Drive_dostats is a script that will run a specified number of copies of dostats against a single database handing each a subset of the tables in that database. Dostats has options, -a/-A and -b/-B, which are used to determine exactly which tables are likely to require the recalculation of their data distributions. On our clients' databases we run dostats every night with -a -A 10 & -b -B 7 (for some larger clients' databases use -A 5 instead). This instructs dostats to run update stats for any table whose row count has changed (up or down) by 10% or whose stats are more than 7 days old. This means that every table is updated at least once a week and more volatile tables more frequently say every 5 days or twice a week. The bottom line is that on any given night only a relatively small number of tables are updated and there is no huge time requirement to run the whole database on any given day. For a very volatile database I would run dostats without the -a and -b options once a month or on the weekend to get everything updated and reduce the size of the daily runs where the run window is typically smaller. Art S. Kagel Oninit > Once again a question to update statistics: Makes it a difference how many > data had changed between two runnings e.g. UPDATE STATISTICS FOR TABLE xxx; > > In other words: Is update statistics aware of the difference or is it > running on all data at all times? > Would it make sense to shorten the interval between two runnings to shorten > the time of the runnings? > And is the behaviour the same for low, medium high (distributions only)? > > The tables in question are rather big (between 10 Million and more than a > billion rows with a couple of indexes) and it's difficult to run update > statistics in an adequate time so I have to find a strategy... > > TIA, > Reinhard. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > >