update statistic session status!

Hi All, I am using the informix 7.3.1 and i am new to it. when i doing the update statistic, i took me 12 hours to complete one single table with only 2.0 sort data, during that 12 hours i found the status of my job (using onstat -g ses 1234) ** 1234 is the session id*** was always in "SLEEPING FOREVER" status. I have no idea what is going on. I already set the PDQpriority 100 and it should take all the resource to run it...and i check the explain plan output file, it said only 53 seconds to complete the job. Anyone can help me what did it do on the other 11 hours and 59 mins???? The table has 90M records, i need to do it everyweek. Thanks in Advance

----LNX_Mon_Jan_27_2003_13:48:08_V3.33-- Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >Datum: 2003.01.27 13:24:07 >Sender: FRANKLIN TANG <franklin@macauslot.com> >Betreff: update statistic session status! [117] > >Hi All, >I am using the informix 7.3.1 and i am new to it. when i doing the updat= e >statistic, i took me 12 hours to complete one single table with only2.0 = sort data, > during that 12 hours i found the status of my job (using onstat -g ses = 1234) >** 1234 is the session id*** was always in "SLEEPING FOREVER" status. >I have no idea what is going on. I already set the PDQpriority 100 and i= t should > take all the resource to run it...and i check the explain plan output f= ile, it said >only 53 seconds to complete the job. >Anyone can help me what did it do on the other 11 hours and 59 mins??? = Was there running anything else with PDQpriority set (another update stat= istics, create index, ...)? There can be only one job running with PDQpriority 10= 0. = Regards, Andreas Kutsche ------------------------------------------------ SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastra=DFe 3 A-5015 Salzburg = Telefon : +43 662 4470 24423 E-Mail : Andreas.KUTSCHE@spar.at Internet: http://www.spar.at ------------------------------------------------ = ----LNX_Mon_Jan_27_2003_13:48:08_V3.33----

U didn't mention the 7.31 version you are on .. if you are on 7.31.UD2 and beyond, the update stats process has been improved with parallism being introduced and possibility of providing more memory using the DBUPSPACE parameter for the SORTs. If you are in a release prior to that, Update stats is a single threaded process so even if you set PDQ its not going to affect it ... if you've got a very huge table, and usually do very basic queries on the table you can try doing an update stats on a NON-INDEXED column and then seeing if there's any affect on the performance or not ... I have got a paper regarding the updated UPDATE STATS ..:-) if you would like to go through it, let me know and I'll forward it to you. Thanx much, Rajib Sarkar Advisory Support Engineer (Wells Fargo Bank) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi "FRANKLIN TANG" <franklin@macaus To: ids@iiug.org lot.com> cc: Sent by: Subject: update statistic session status! [117] forum.subscriber @iiug.org 01/27/2003 02:27 AM Hi All, I am using the informix 7.3.1 and i am new to it. when i doing the update statistic, i took me 12 hours to complete one single table with only 2.0 sort data, during that 12 hours i found the status of my job (using onstat -g ses 1234) ** 1234 is the session id*** was always in "SLEEPINGFOREVER" status. I have no idea what is going on. I already set the PDQpriority 100 and it should take all the resource to run it...and i check the explain plan output file, it said only 53 seconds to complete the job. Anyone can help me what did it do on the other 11 hours and 59 mins???? The table has 90M records, i need to do it everyweek. Thanks in Advance

----- Original Message ----- From: Rajib Sarkar <rsarkar@us.ibm.com> At: 1/27 11:16 Rajib, while the UPDATE STATISTICS table scanning process does not use PDQ prior to 7.31 the sorting process DOES take advantage of PDQ in earlier versions if you enable the PSORT package using PSORT_NPROCS and PSORT_DBTEMP. FWIW this can make a dramatic difference in the runtime of UPDATE STATISTICS on large tables. Art S. Kagel U didn't mention the 7.31 version you are on .. if you are on 7.31.UD2 and beyond, the update stats process has been improved with parallism being introduced and possibility of providing more memory using the DBUPSPACE parameter for the SORTs. If you are in a release prior to that, Update stats is a single threaded process so even if you set PDQ its not going to affect it ... if you've got a very huge table, and usually do very basic queries on the table you can try doing an update stats on a NON-INDEXED column and then seeing if there's any affect on the performance or not ... I have got a paper regarding the updated UPDATE STATS ..:-) if you would like to go through it, let me know and I'll forward it to you. Thanx much, Rajib Sarkar Advisory Support Engineer (Wells Fargo Bank) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi "FRANKLIN TANG" <franklin@macaus To: ids@iiug.org lot.com> cc: Sent by: Subject: update statistic session status! [117] forum.subscriber @iiug.org 01/27/2003 02:27 AM Hi All, I am using the informix 7.3.1 and i am new to it. when i doing the update statistic, i took me 12 hours to complete one single table with only 2.0 sort data, during that 12 hours i found the status of my job (using onstat -g ses 1234) ** 1234 is the session id*** was always in "SLEEPINGFOREVER" status. I have no idea what is going on. I already set the PDQpriority 100 and it should take all the resource to run it...and i check the explain plan output file, it said only 53 seconds to complete the job. Anyone can help me what did it do on the other 11 hours and 59 mins???? The table has 90M records, i need to do it everyweek. Thanks in Advance