RE: PDQ

Great paper ! a few months ago i got an error when running update statistics for a very big table :"Not enough space for sorting" ... or something like that . My IDS Version is 7.31.UD1 How can I avoid this error? By Using DBUPSPACE environment Variable ? Thanks in advance Regards -----Mensaje original----- De: Rajib Sarkar [mailto:rsarkar@us.ibm.com] Enviado el: Jueves, 23 de Octubre de 2003 08:53 a.m. Para: Mark D. Stock CC: informix-list@iiug.org; Obnoxio The Clown; owner-informix-list@iiug.org Asunto: Re: PDQ Actually, Obnoxio is kind of right ..:-) Although this parameter is not new, this parameter has been "upgraded" to accomodate a) More memory for sorting b) To print out the progress of update stats (explain plan). Previously, the default was 4MB for sorting ...but now its 15MB default and it can be set to 50MB by using DBUPSPACE but if you want more than that and do it in parallel u will hv to use the PDQPRIORITY variable setting in order to control that. I am attaching a white paper I had written on the same subject ... HTH (See attached file: UpdateStatisticsHeadsupArticle.doc) Thanx much, Rajib Sarkar Advisory Software Engineer (RAS) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 T/L : 667-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi "Mark D. Stock" <mdstock@MydasSol To: Obnoxio The Clown <obnoxio@hotmail.com> utions.com> cc: informix-list@iiug.org Sent by: Subject: Re: PDQ owner-informix-li st@iiug.org 10/23/2003 01:50 AM Obnoxio The Clown wrote: > David E. Grove wrote: > >>>makes a big difference to stuff like index creation and update stats. >> >>We learned to be careful with PDQ and UPDATE STATISTICS. > > There is a new environment variable (DBUPSPACE?) which, when used with PDQ > will allocate more memory for the update statistics process which > means that sorting of the index data will take place in parallel, > which is much > faster. However,what you're describing is the documented behaviour of > SP compilation. So yes, you don't want to do this for SPs, but you do > want to > do it for data. No no no, it certainly isn't DBUPSPACE. That is a very old parameter that LIMITS the amount of memory that UPDATE STATISTICS can use to 4Mb. Unless they've changed it. Speaking of old parameters, you can also set PSORT_NPROCS to help optimise parallel sorts during UPDATE STATISTICS. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+ sending to informix-list sending to informix-list