Re:Update statistic question

Susan, The order in which you are doing the update statistics is correct. The reason it is taking so long is because of the number of tables that you have, and also that it is serial. I had a problem when I reversed the low and the medium,and that ended up overwriting the everything !!. So DON'T change the order. Here are a couple of things that you can do. 1. Parallelize them into multiple scripts ( one script per table ), which is ideal, but may not work in your case, since you have 13635 tables ( How did it ever get so many ?? ). 2. Use a different method to parallelize the scripts. Here is what I do, and you can take some ideas from it. I have a 1000 tables, and can't create 1000 scripts. So here goes. I isolate all the big tables ( tables > 1000000 rows ). Create individual scripts to run the "high for table(column) " for all columns heading an index. Which means that I have one script each for the "high" for "large" tables. I then create multiple scripts for the "highs" for the rest of the tables I then create one single script for the lows for ALL the tables ( ie lows on entire index key for each index ) I then create one single script for the "medium distributions only" I keep a limit on the number of processess that I can run ( which in my case is 20 ). I have 8 CPUs, and I am pretty sure I can do better than that. My only recommendation is trial and error. So, when I create these scripts- which could total to more than 20, I then set them off 20 at a time. The only thing that I regulate is the medium and the low, ie I set off the highs only AFTER the medium is finished, and I set off the LOW only AFTER ALL the HIGHS are finished. The medium takes about 1.5 hours, the highs ( parallel scripts ) take about 4 hours total, and the low takes just a few minutes, so my 24 hours process is done in about 6 hours. I run this during the weekend when there are only a few users. By the way, I have a 4GL program to create these scripts and run them. I am enclosing a message that I had posted to this newsgroup a few weeks back, and Art Kagel's response - which I absolutely rely on. I hope this helps Sujata *** OLD MESSAGE ENCLOSED *** 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 22hours, 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 ____________________Reply Separator____________________ Subject: Update statistic question Author: "Susan Elliott (ISG)" <SusanE@fclcis.co.nz> Date: 4/22/98 6:23 PM Thanks to all of those who have been answering my questions over the last few days !!! Its much appreciated !!! I have a question about the Update statistics script that we do here.... #### Beginning of script ##### set isolation dirty read; update statistics medium distributions only; update statistics high for table (Table name) (Column Name)distributions only; {We do the update statistics high on 13635 tables, like above} update statistics low;##### End of script ###### This was set up for me. I have worked out that the high over writes the mediums on the tables/ columns specified. It does an update low across the whole database. Does this mean that the lows have over written the highs ??? Should I change the order of this to low, med and then to the highs ?? Or to med, low and then the highs ?? My next stumbling block is the above script runs sequentially and takes over 2 days to complete. (we don't run it very often now) So that we can run this more often..... and maybe we will get better performance... What are my options in breaking up the above script ???? What do other folk do ??? Can I just break the highs script up and run several at once ??? Can split it up into table types... general ledger, accounts payable, sales etc and do the low, med and then the high on each table types ??? How many scripts I run at once ??? What is this dependant on ??? physical cpus ? cpu vps ?? Honestly, any help with this would be much appreciated, Thank in advance Best Regards Suze.