RE: Update staistics Running Way Slow

>===== Original Message From "Mark D. Stock" <mdstock@mydas.freeserve.co.uk> ===== >Sanjeev sagar wrote: >> >> This is a multi-part message in MIME format. >> --------------FBF95EAB01AE2EFCAE40C116 >> Content-Type: text/plain; charset=us-ascii >> Content-Transfer-Encoding: 7bit >> >> Hello All, >> >> I am running Update Stats on ne fragmented table in 40 >> dbspaces and looks like it's running way slow. Please >> look at my follwing script, even after setting >> PDQPRIORITY, I am not able to see anything in onstat -g >> mgm. Because of this I am guessing that I am getting >> default allocation which is 128K. I will highly >> appreciate any attention and some tips to improvethe >> timing of this. I am getting 1hr 15min. > >No, UPDATE STATISTICS doesn't use PDQ at all AFAIK. This may have >changed in 9.20? > >The normal solution, providing you have the resources, is to run >multiple UPDATE commands. You could for instance allocate one command >per disk. However, in your case, you are only updating one table, so >even this is not possible. > >You could try reducing the CONFIDENCE so that the sample size is >smaller. > >Cheers, >-- >Mark. > >+----------------------------------------------------------+-----------+ >| Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| >| http://www.informix.com http://www.informixhandbook.com |///// / //| >| http://www.iiug.org +-----------------------------------+//// / ///| >| |This email will self-destruct in |/// / ////| >| |10 sec. If you received this email |// / /////| >| |in error, sorry about the mess. |/ ////////| >+----------------------+-----------------------------------+-----------+ A couple of thoughts. 1. From the Performance guide -- The SQL UPDATE STATISTICS statement, which is not processed in parallel, is affected by PDQ because it must allocate the memory used for sorting. Thus the behavior of the UPDATE STATISTICS statement is affected by the memory management associated with PDQ. Even though the UPDATE STATISTICS statement is not processed in parallel, the database server must allocate the memory that this statement uses for sorting. -- 2. Couldnt he run one update statistics for each column? If they all started at the same time, they would be sharing the buffers which whoever is in the lead pulls in, but be able to run across multiple CPU's. Kind of like starting multiple batch jobs which process the same file sequentially on a multi cpu box... as long as your processes dont get out of sync you only do one disk read, the rest is cached. I dont have a big enough test box to test this theory, but thoughts on how this might perform would be appreciated. Hope this helps, Will ------------------------------------------------------------ This e-mail has been sent to you courtesy of OperaMail, as a free service from Opera Software, makers of the award-winning Web Browser, Opera. Visit us at http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail account is waiting at: http://www.operamail.com/ ------------------------------------------------------------