Re: Update Statistics Speedup Help???
You The Man Art - exactly what I was looking for - I knew you would have
it well doped out. David - this looks like it will be a good candidate
for the FAQ soon.
I do have a couple of questions:
1. According to the 7.1 doc, PSORT_NPROCS max thread value is 10 - what
is the effect of setting it very large??
2. What is PSORT_MAXALLOC - I can't find any doc for it.
Thanks for the tip about the ontape/onbar bug - I hadn't been bitten by
it yet....
Thanks - Greg
Art S. Kagel wrote:
snip....
.> 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