Re: PDQ

On Thu, 23 Oct 2003 04:50:06 -0400, Mark D. Stock wrote: > 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. Yes, the meaning of DBUPSPACE changed when the sort package's memory handling was improved in 7.31UD4 and later (I think 9.30 and later also). Check out Jon Miller II's white paper on optimizing Update Statistics runs. > Speaking of old parameters, you can also set PSORT_NPROCS to help optimise > parallel sorts during UPDATE STATISTICS. Absolutely! And if you have a version prior to 7.31UD4 (or 9.30) PSORT_DBTEMP can help too by using unflushed filesystem space and the system buffer cache to speed sorts that need to go to disk (the new sort package rarely goes to disk). Art S. Kagel

"Art S. Kagel" wrote: > > On Thu, 23 Oct 2003 04:50:06 -0400, Mark D. Stock wrote: > > > 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. > > Yes, the meaning of DBUPSPACE changed when the sort package's memory handling > was improved in 7.31UD4 and later (I think 9.30 and later also). Check out Jon > Miller II's white paper on optimizing Update Statistics runs. is there a reason why the documentation of 9.40 somewhat is different from Jon Miller II's explanation of the parmeters one can feed into this variable? V9.40 documentaion does not mention how to avoid index scans ...... > > > Speaking of old parameters, you can also set PSORT_NPROCS to help optimise > > parallel sorts during UPDATE STATISTICS. > > Absolutely! And if you have a version prior to 7.31UD4 (or 9.30) PSORT_DBTEMP > can help too by using unflushed filesystem space and the system buffer cache to > speed sorts that need to go to disk (the new sort package rarely goes to disk). > > Art S. Kagel dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe