Re: ORDER OF UPDATE STATS : A FOLLOW-UP

Hi Everybody, This is excellent summary after the problem is solved. One of the purpose of joining the list and reading everybody elses problem is to learn from other experience. This type of summary are good learning and we should make it a point to summarize our problem and the solution after it is solved and mail it to everybody. The time spent will really help everybody or atleast many of us. Thank you Sujata. Sunil Thakkar. ================================ From: ssoman@omm.com Date: Tue, 07 Apr 98 09:20:01 -0800 To: <informix-list@iiug.org> Subject: ORDER OF UPDATE STATS : A FOLLOW-UP Hi all, Thanks to everyone who responded so quickly to my urgent question on the order of update statistics. To make a long story short, the mistake I did was as follows:- Since I ran the MEDIUM ( distributions only ) concurrently with other scripts running the HIGH, the MEDIUM effectively wiped out all index statistics on tables that the HIGH has so painstakingly created, and only those tables( index columns ) which managed to run after the MEDIUM had completed stood a chance at retaining their information. A lot of you suggested that I ran one script per table, but my systsem has over 900 tables !! ( Don't ask me, like all good DBA's, it happened way before my time !! ) Anyway. I usually run about 20 concurrent scripts which work very well, and I cannot afford to do more. Which means, that I have to run ONE SINGLE MEDIUM script, taking care to run it FIRST. I also found that putting the LOWS ( on columns not heading an index ) into one script file is very quick. Now all I am left with is the HIGHS. I have a few ( about 15 ) really large tables, which take for ever on the UPDATE STATISTICS HIGH. Hence I parallelize them ( ie put each table onto an individual script ). The rest of the HIGHS are split into one or more script files. The count of the script files never exceeds the max number of processes ( in my case 20 ), and if it does, the processes wait till an executing processes completes. The MEDIUM, I have noticed takes about 1.5 hours, the LOW about 10 minutes, and the HIGHS should not take more than 4 hours total. Which brings my total timing to about 6 hours. Which is a heck of a lot better than the current 20 + hours which it is taking ( ignore my earlier mail where I said that it takes 12 hours, that was last year !! ) I hope this new procedure will do the trick. I will test the results on my dev box and let you know. By the way, a quick question : HOW do I know if the job has been done correctly ? Is it enough if I check that there is atleast a "HIGH" and a "MEDIUM " in the sysdistrib table for the said table, said date ?? Thanks again Sujata ***************************************** Sujata Soman E-Mail: ssoman@omm.com O'Melveny & Myers, Los Angeles ****************************************** ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com