Re: URGENT:Q on Order of Update Stats

ssoman@omm.com wrote: > > Hi all, > I was following the informix recommendations (ver 7.2 ) for running UPDATE > STATISTICS on our database, ie. MEDIUM, HIGH on columns heading an index, and > LOW on all other columns of an index. This process is run once a week, and since > it takes over 12hours, I tried to run multiple concurrent sessions of it. My > question is this :- > 1.Should the MEDIUM be run BEFORE the HIGH/LOW ? > 2. Running the MEDIUM after the HIGH, removes all statistical information > for the table from sysdistrib , ie it replaces the HIGH information with the > MEDIUM. Does this affect anything ? Have I lost the whole purpose of doing a > HIGH ?? > This last weekend, I split the whole processess into 10 different sessions, > put all the lows into one session, put some of the big tables ( HIGH ) into 9 > different sessions, and the medium into another session. I waited for the low to > finish before I ran the MEDIUM, but at the end of it all ( which took only an > incredible 3 hours ), I do not see any information in SYSDISTRIB for a HIGH ( > except for a few tables ). Also, my month-end processess took FOR EVER to finish > this weekend. > SO, have I messed up everything ? If I have, I need to correct it by the > next two days. What is the order to be followed to run UPDATE STATISTICS ?? Yes, you "messed up". The MEDIUM did indeed UNDO all that the HIGH stats did. You want to run the MEDIUM on the table with the DISTRIBUTIONS ONLY option, THEN run HIGH for each of the leading columns of each index with DISTRIBUTIONS ONLY, (optionally also run HIGH...DISTRIBUTIONS ONLY on any columns of multi-column indexes that follow a lead column that heads up more than one index), THEN run LOW for the entire index key for each index (DO NOT INCLUDE THE DROP DISTRIBUTIONS CLAUSE!). For single key indexes you can eliminate the LOW on the key by removing the DISTRIBUTIONS ONLY clause from the corresponding HIGH. Also note that if a column leads more than one index you only have to do the HIGH for that column once. There is a utility (dostats.ec) that is in my contribution to the IIUG Software Repository (file: utils_ak also later this week I will be contributing another file <utils2_ak> that will include an updated version of dostats.ec with more features) that will automate this. The best way to parallelize the stats is to make an SQL script for each table that has all of the UPDATE STATISTICS commands for that table in the proper order and run several tables in parallel from separate shell scripts. If you SET LOCK MODE TO WAIT 10 in each script that will be sufficient to prevent the various scripts from locking each other out of the system tables if they happen to be finishing at the same time. Or you could run several copies of dostats on individual tables from multiple scripts in parallel. Art S. Kagel