Re:RE: URGENT:Q on Order of Update Stats

Thanks to all who responded to my urgent question. It would be great if I could write scripts for individual tables and run them, but my database has about 900 tables !! ( Don't ask me !! ), and grouping the LOWs ( for columns not heading an index ) for all tables into one script file works really quick. Which means that I HAVE to run one command for the entire database for the MEDIUM DISTRIBUTIONS ONLY. I have split the HIGH into different script files, with the only difference that the really BIG tables ( > 1000000 rows - and trust me some tables are BIG ) into individual script files, and the rest ( < 1000000 rows ) divided into more script files. All this done by maintaining the count of the total number of concurrent scripts that I can run. After seeing all your responses and realising the mistake that I made, I am going to run the MEDIUM first, then run all the HIGHs, and once they are all done, run the low. The MEDIUM alone takes about 1.5 hours, the LOW about 10 minutes, and all the HIGHS combined together should not take more than 4 hours. Which puts the total to less than 6 hours, which is much better than the original 20 + hours ( my earlier mail said 12, but a quick look again at my log shows otherwise ). I hope this should do the trick. ____________________Reply Separator____________________ Subject: RE: URGENT:Q on Order of Update Stats Author: "Richard Thomas" <richard_thomas@yes.optus.com.au> Date: 4/7/98 8:58 AM 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 ?? > > As always, I appreciate your response(s) > > Thanks > > Sujata Sujata: It sounds like something has stuffed up. What is important is to make sure one process does everything relating to any one table, ie don't put LOWs in one, MEDIUMs (media?) in another etc. Write it in such a way that each process will be always operating on a different table, and you can be sure that the results will be the same as if you just ran one long SQL. Just faster ;-) The book says MEDIUM DISTRIBUTIONS ONLY, then HIGH (by column), then LOW. I would follow that. I use a script to create and load a table of table-names, as well as a "size indicator" - rowsize * nrows (this all comes from systables). The script then launches 'n' processes of the same 4GL executable in the background, each of which pick off tables from this list in descending size order, and complete all the statistics. Doing it like this ensures that irrespective of how many concurrent processes you have they should share the work reasonably equally. You should be able to duplicate this effect by generating 5 scripts. I use 4GL because it generates all the UPDATE STATISTICS statements for me from systables as it executes. [ Pop Quiz: write the SQL to identify the columns which are part of an index, but not the leading column of one. Don't forget about indexes sorted in descending order... ;-) ] I usually run 5 concurrent processes. I have tried other numbers, but any more doesn't seem to improve results. (6 cpu machine) cheers RET +------------------------------------------+ | Richard Thomas | | DBA - Marketing Information Systems | | Optus IT | | email: richard_thomas@yes.optus.com.au | | Ph: +61 2 9342 7188 | | "My opinions are my opinions" | +------------------------------------------+