Re: Mysterious update statistics

Hi Art. First thank you for the detailed answer. I read the manuals and the paper of John Miller now again but some questions remain. I use your dostats to generate UPDATE STATISTICS statements putting them in a file which I run periodically. I never used parallelism because the servers run a high workload 24x7. With a new server I try to use your drive_dostats utility. There is only one database on this server, though tables with more than a billion rows. Version of IDS is 9.40 FC9W2 on Solaris 8, 9, and 10. Host has 16 processors and 32 GB memory, 15 CPU VP's are configured, IDS uses 6.476.800 Kbytes, SHMTOTAL is 12000000, DS_TOTAL_MEMORY is 300000. - I have set DBSPACETEMP in onconfig to four exclusive temp dbspaces. Have I to set it in the environment again? - When running dostats I see the message "Warning! Optimized servers produce statistics fastest with PSORT_DBTEMP set." Manuals don't recommend to set PSORT_DBTEMP but DBSPACETEMP what is done in onconfig. Do I miss something here? - I use -a -A 20 and -b -B 5 meaning tables are processed if the last run on the table is older than 20 days _or_ the number of rows has changed more than 5 percent. Is this true especial for the _or_? - Is DBUPSPACE of any use if PDQPRIORITY ist set? - For running dostats I call drive_dostats in a little shell script which I want to run via crontab frequently: . /home/informix/.informix.rc # Informix environment PATH=$PATH:/home/informix/tools/utils2_ak; export PATHDBUPSPACE=50; export DBUPSPACE PSORT_NPROC=4; export PSORT_NPROC PDQPRIORITY=50; export PDQPRIORITY ### drive_dostats nprocs database [tablespec] [-x@excl|-xexctbl] [dostats options] drive_dostats 5 kva -xkva_ergebnisse_alt -a -A 20 -b -B 5 -X -v 2 -E -F -R 0.1 -r 1.0 -m Did I set the necessary environment variables and is the invocation of dostats rather reasonable? Regards, Reinhard. > -----Ursprüngliche Nachricht----- > Von: informix-list-bounces@iiug.org > [mailto:informix-list-bounces@iiug.org]Im Auftrag von Art S. Kagel > (Oninit) > Gesendet am: Montag, 14. April 2008 16:10 > An: Habichtsberg, Reinhard > Cc: Informix-List (E-Mail) > Betreff: 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/libr > ary/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 > > > > > > > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >