Re: Update Statistics Speedup Help???

David Williams wrote: > > In article <34BA78EB.3482@eds.com>, Greg Moye <greg.moye@eds.com> writes > >Hey gang, anybody have any good suggestions for things I can do to speed > >up UPDATE STATISTICS SQL??? > > > >I have a few constraints though: I'm reorging SAP dbspaces with SAPDBA, > >so I don't have access to the code and can't overhaul it to suit > >myself. Generally, SAPDBA follows the Informix suggestions (HIGH for > >lead columns in an index etc...). Whatever I do needs to be at the > >engine configuration level and invisible to SAPDBA. > > > >It appears that the UPDATE STATS phase of the reorg is the slowest, so > >any ideas to speed it up are appreciated. > > > >Thanks- Greg > > Run it wiht a higher PDQPRIORITY. Allocate many temp dbspaces spread > across disks (1 temp dbspace per disk). Be aware that there have been > problems with unevenly sized temp dbspaces and when one fills up > Online does not use the next one but gives an error to your session. > I would size all your temp dbspaces evenly and not make them too > small. > > Fragmenting tables more may help, allowing more scan threads to be > generated hence increases parallelism (Art - bet you have some useful > experince here, one for the FAQ please??). Thought I put my $0.02 in on this one. OK. 1) UPDATE STATS on individual tables NEVER databases. 2) Follow the rules in SERVERS_7.2 (if you do not have that file or your release was one of those that is just a clone of SERVERS_7.1 let me know I have extracted the text and can post it.) 3) Set PDQPRIORITY to at least 10 (go for 40 if you can run during light user load. 4) Set PSORT_NPROCS>=40 5) Set PSORT_MAXALLOC=10240 6) Either use AT LEAST 4 TEMPDBspaces equally sized or set PSORT_DBTEMP to list AT LEAST 3 different file systems on different drives again keeping in mind that the one with the least available space limits sort space. As far as sizing and number of tempdbspaces MORE and LARGER is the rule. More spaces enhances parallelism and spreads the I/O load across more disks and controllers but until 7.3 fixes the archive problem fewer and larger will keep your archives from failing due to lack of temp space. So for now if you have N bytes of disk available for temp spaces use them to create 3-4 temp spaces if N/4 is big enough to hold the update activity for your M/4 most active tables (where M is the total number of tables) throughout an entire archive (if ontape takes 8 hours to run you need space for 8 hours of changed pages) and 2-3 temp spaces if N/4 is not large enough. Once 7.3 is released temp tables will be fragmented across all tempspaces even the temp tables created by ontape/onbar to hold modified pages so we can break up the temp spaces again. Art S. Kagel