Re: ORDER OF UPDATE STATS : A FOLLOW-UP

Hi all: I hope the following information will be usefull to you all. ***************************************************************************** *** PERFORMANCE DEGRADATION TECH ALERT *** ***************************************************************************** Potential performance degradation after executing update statistics medium or high on Informix Dynamic Server. Affected versions are 7.23.UC9, 7.23.UC10 and 7.24.UC4. This includes all Unix and NT platforms. Symptom: - Transaction processing rate may decrease dramatically after executing update statistics high or medium. - No error message is associated with this problem. When it happens: Processing transactions after performing update statistics high or medium. Solution: The problem will be fixed in the upcoming 7.23.UC11 and 7.24.UC5 releases. ***************************************************************************** At 03:53 PM 4/7/98 CDT, you wrote: >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 > > -###################################################################-####### -Selvakumar Rajagopal -Database Specialist Endura Software Corporation -Technical Service Group Seattle, WA - Phone:(206) 522-0055 - Fax:(206) 522-0053 - E-mail: -selvakumar.rajagopal@enduracorp.com - Web: http://www.enduracorp.com -###################################################################-#######