Re: Update staistics Running Way Slow

William Rice wrote: > > >===== 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. Mmmm, I'll have to test that theory when I get a spare moment. :-) > 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. Not a bad idea, but what happens if they DO get out of sync? That disk is going to get hammered! You are also relying on exclusive use of the buffers, or at least no other users flushing your buffers. :-) 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. |/ ////////| +----------------------+-----------------------------------+-----------+