Update stats impacts insert performance
A 24/7 OLTP site on IDS 7.31 ran weekly UPDATE STATISTICS MEDIUM DISTRIBUTIONS ONLY plus HIGH on a 1-billion-row insert-only fragmented table; the job grew to days and its CPU/memory use delayed inserts. Art Kagel recommended John Miller's update-stats paper and his dostats utility, and gave a tailored command set: MEDIUM on non-leading columns, HIGH (distributions only) on leading columns, and one LOW over the index key columns. Tuning advice: raise PDQPRIORITY (needed for parallel sorts), set PSORT_NPROCS to ~2x CPU VPs, use PSORT_DBTEMP filesystems, increase MGM sort memory (DBUPSPACE only applies when PDQ=0), and run the full suite less often (e.g. quarterly) with frequent HIGH only on the serial column plus a LOW. Isolation level was said to make no difference. No confirmation of results was posted.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
My customer has a large database table (over 1000,000,000 rows) stored in
Informix 7.31FD7-1 on a Unix server.
The table forms an active part of a 24/7 OLTP production system that handles
around 860,000 inserts per day.
No data is deleted or updated in this table, it is only inserted.
We run an 'UPDATE STATISTICS MEDIUM FOR TABLE xxx DISTRIBUTIONS ONLY' command
followed by 'UPDATE STATISTICS HIGH FOR TABLE xxx (aaa, bbb, ccc, ddd)'
command on this table at 02:00 on every Sunday. {where xxx = table name and
aaa, bbb, ccc and ddd are indexed columns in the table.)
When first implemented, this job completed before Monday morning, (when the
system starts to be used much more intensively.) however this update stats job
can now still be running on Tuesday, or even Wednesday morning. We assume that
this is due to the steadily increasing size of this table.
We have recently found that when the job nears completion, it starts to use
too many resources on the server, (CPU and to some extent memory.) in turn
this system loading can cause data inserts to be delayed by 20 minutes or
more. This delay is unacceptable to our customer.
Our main question is: How best can we shorten the length of time taken for
this update statistics job to run, and/or prevent this update from impacting
on insertion of data performance?
Our current thinking is to Run the update statistics in LOW mode, followed by
MEDIUM DISTRIBUTIONS ONLY. Not running HIGH at all.
So the commands run, in order, will be:
UPDATE STATISTICS LOW FOR TABLE xxx (aaa, bbb, ccc, ddd)
UPDATE STATISTICS MEDIUM FOR TABLE xxx DISTRIBUTIONS ONLY
Quoting from the Informix manual: 'LOW - updates systables, syscolumns and
sysindexes catalog tables'
'MEDIUM - does a LOW and updates sysdistrib catalog table'
'DISTRIBUTIONS ONLY leaves existing index information in place'
- I am assuming that 'leaves existing index information in place' means all
the updates LOW has made in this context. So I get a 'LOW' statistic set plus
a sampled sysdistrib statistic?
We are not proposing to alter the RESOLUTION clause from its default setting.
We think that this will complete more quickly, but will it slow the query
response time on this table appreciably ?
Are there other methods we should consider ?
Do we need to update statistics at all on a table into which we only insert
data ? Is one single run of update stats enough when the table is first
created, or do we need to do this again on a weekly/monthly basis ?
Thanks for any help,
Regards,
Howard Anderson
If you post the list of all indexes (including any constraints supported by
hidden indexes) I will post my recommended set of commands.
What you are currently doing was optimal long ago, however, since IDS v
7.31xD3 (and 9.30xC2) and all newer servers, there are more efficient ways
to gather the required statistics in minimal time and with minimal impact.
Also note that several environment variables will positively or negatively
affect the runtimes of your update statistics commands and you may not be
configuring these to take best advantage of the resources you have
available. Please read John Miller III's white paper that details the
changes made to these newer servers and how to take advantage of them to
speed up processing of UPDATE STATISTICS (link below) and also note that my
dostats utility implements the protocols that John describes automatically
for you (though you still have to set the environment and server
configuration properly for best results). Dostats is part of the package
utils2_ak which you can download from the Oninit web site (
www.oninit.com/utils) or the IIUG Software Repository (www.iiug.org/software
).
Link to John's paper:
Understanding and Tuning Update
Statistics<http://www.ibm.com/developerworks/db2/zones/informix/library/techarti
cle/miller/0203miller.html>:
http://www.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/
0203miller.html
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Wed, Aug 19, 2009 at 1:19 PM, HOWARD ANDERSON <
howard.anderson@edl.uk.eds.com> wrote:
> My customer has a large database table (over 1000,000,000 rows) stored in
> Informix 7.31FD7-1 on a Unix server.
> The table forms an active part of a 24/7 OLTP production system that
> handles
> around 860,000 inserts per day.
> No data is deleted or updated in this table, it is only inserted.
>
> We run an 'UPDATE STATISTICS MEDIUM FOR TABLE xxx DISTRIBUTIONS ONLY'
> command
> followed by 'UPDATE STATISTICS HIGH FOR TABLE xxx (aaa, bbb, ccc, ddd)'
> command on this table at 02:00 on every Sunday. {where xxx = table name and
> aaa, bbb, ccc and ddd are indexed columns in the table.)
>
> When first implemented, this job completed before Monday morning, (when the
> system starts to be used much more intensively.) however this update stats
> job
> can now still be running on Tuesday, or even Wednesday morning. We assume
> that
> this is due to the steadily increasing size of this table.
> We have recently found that when the job nears completion, it starts to use
> too many resources on the server, (CPU and to some extent memory.) in turn
> this system loading can cause data inserts to be delayed by 20 minutes or
> more. This delay is unacceptable to our customer.
>
> Our main question is: How best can we shorten the length of time taken for
> this update statistics job to run, and/or prevent this update from
> impacting
> on insertion of data performance?
>
> Our current thinking is to Run the update statistics in LOW mode, followed
> by
> MEDIUM DISTRIBUTIONS ONLY. Not running HIGH at all.
>
> So the commands run, in order, will be:
>
> UPDATE STATISTICS LOW FOR TABLE xxx (aaa, bbb, ccc, ddd)
> UPDATE STATISTICS MEDIUM FOR TABLE xxx DISTRIBUTIONS ONLY>
> Quoting from the Informix manual: 'LOW - updates systables, syscolumns and
> sysindexes catalog tables'
>
> 'MEDIUM - does a LOW and updates sysdistrib catalog table'
>
> 'DISTRIBUTIONS ONLY leaves existing index information in place'
>
> - I am assuming that 'leaves existing index information in place' means all
> the updates LOW has made in this context. So I get a 'LOW' statistic set
> plus
> a sampled sysdistrib statistic?
>
> We are not proposing to alter the RESOLUTION clause from its default
> setting.
>
> We think that this will complete more quickly, but will it slow the query
> response time on this table appreciably ?
> Are there other methods we should consider ?
> Do we need to update statistics at all on a table into which we only insert
> data ? Is one single run of update stats enough when the table is first
> created, or do we need to do this again on a weekly/monthly basis ?
>
> Thanks for any help,
>
> Regards,
>
> Howard Anderson
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015174bdcc8b72a55047181f917
Art,
Many thanks for the prompt reply,
I enclose a snippet of the schema dump for this table, showing the index
definitions.
As you can see a primary key is defined on multiple columns, but I know of no
other 'hidden' index constraints.
Thanks for the link to John's paper, I had already read this, and was running
some tests using the PDQ environment variables, but I did not see much (if
any) gain. Possibly this is due to insufficent available physicial memory on
the system. 'Set explain' showed that the sort data was 32Gb for HIGH, and
41Mb was available when PDQ was enabled. I only have 4Gb of physical memory
installed, and much of this has already been devoured by the database. I also
only have 2 processors in this system, so PSORT_NPROCS is only set to 2. I am
going to set DBUPSPACE=0:50:0 as this gives 50Mb.
Chuck Berghoff also mentioned use of PDQ to utilise the Memory Grant Manager,
and mentions PSORT_DBSPACE, which I have not yet tried, as I have DBSPACETEMP
set in onconfig, however it may be worth trying PSORT_DBSPACE to override
this, just for the update stats. I am using a Compaq ES40 Alphaserver and this
would seem comparable to his Sun V490, however he is using V10.00.FC5, and
this may be giving an improvement. (I cannot upgrade on my current hardware.)
I will download a copy of your dostats utility and give it a try.
A colleauge on an internal company forum pointed out to me that each week only
a very small percentage of the data in the table has changed, (1bn to 1.001bn)
so a weekly frequency for update sats on this table is too high. He has
suggested that a biannual frequency may be more appropriate. My only concern
with this is that it could still impact performance at that point. The root
problem is the performance impact on insertion. Is it possible to 'nice' the
update stats process in some way ? (Reduce its use of system resorces, at the
expense of time taken.)
Would setting a 'DIRTY READ' isolation level have any performance benefit ? Or
would this just produce inaccurate stats ?
Schema dump:
(I have obfuscated the folowing somewhat, as I don't want to expose the real
table or column names.)
{ TABLE "informix".xxx row size = 122 number of columns = 12 index size = 70
}
create table "informix".xxx
(
aaa varchar(12),
bbb datetime year to second,
ccc char(2),
eee char(1),
fff char(2),
ggg char(2),
hhh smallint,
iii integer,
jjj char(2),
kkk varchar(80),
lll char(1),
ddd serial not null ,
check (lll IN ('Y' ,'N' ))
)
fragment by expression
((MONTH (bbb ) = 1 ) AND (DAY (bbb ) <= 15 )
) in fragm001 ,
((MONTH (bbb ) = 1 ) AND (DAY (bbb ) > 15 ) )
in fragm002 ,
((MONTH (bbb ) = 2 ) AND (DAY (bbb ) <= 15 )
) in fragm003 ,
((MONTH (bbb ) = 2 ) AND (DAY (bbb ) > 15 ) )
in fragm004 ,
((MONTH (bbb ) = 3 ) AND (DAY (bbb ) <= 15 )
) in fragm005 ,
((MONTH (bbb ) = 3 ) AND (DAY (bbb ) > 15 ) )
in fragm006 ,
((MONTH (bbb ) = 4 ) AND (DAY (bbb ) <= 15 )
) in fragm007 ,
((MONTH (bbb ) = 4 ) AND (DAY (bbb ) > 15 ) )
in fragm008 ,
((MONTH (bbb ) = 5 ) AND (DAY (bbb ) <= 15 )
) in fragm009 ,
((MONTH (bbb ) = 5 ) AND (DAY (bbb ) > 15 ) )
in fragm010 ,
((MONTH (bbb ) = 6 ) AND (DAY (bbb ) <= 15 )
) in fragm011 ,
((MONTH (bbb ) = 6 ) AND (DAY (bbb ) > 15 ) )
in fragm012 ,
((MONTH (bbb ) = 7 ) AND (DAY (bbb ) <= 15 )
) in fragm013 ,
((MONTH (bbb ) = 7 ) AND (DAY (bbb ) > 15 ) )
in fragm014 ,
((MONTH (bbb ) = 8 ) AND (DAY (bbb ) <= 15 )
) in fragm015 ,
((MONTH (bbb ) = 8 ) AND (DAY (bbb ) > 15 ) )
in fragm016 ,
((MONTH (bbb ) = 9 ) AND (DAY (bbb ) <= 15 )
) in fragm017 ,
((MONTH (bbb ) = 9 ) AND (DAY (bbb ) > 15 ) )
in fragm018 ,
((MONTH (bbb ) = 10 ) AND (DAY (bbb ) <= 15 )
) in fragm019 ,
((MONTH (bbb ) = 10 ) AND (DAY (bbb ) > 15 )
) in fragm020 ,
((MONTH (bbb ) = 11 ) AND (DAY (bbb ) <= 15 )
) in fragm021 ,
((MONTH (bbb ) = 11 ) AND (DAY (bbb ) > 15 )
) in fragm022 ,
((MONTH (bbb ) = 12 ) AND (DAY (bbb ) <= 15 )
) in fragm023 ,
((MONTH (bbb ) = 12 ) AND (DAY (bbb ) > 15 )
) in fragm024
extent size 128000 next size 128000 lock mode row;
revoke all on "informix".xxx from "public";
create unique index "informix".ix_x_aaa on "informix".xxx
(aaa,bbb,ccc)
fragment by expression
(aaa [1,1] <= 'A' ) in fragm001 ,
(aaa [1,1] = 'B' ) in fragm002 ,
(aaa [1,1] = 'C' ) in fragm003 ,
(aaa [1,1] = 'D' ) in fragm004 ,
(aaa [1,1] = 'E' ) in fragm005 ,
(aaa [1,1] = 'F' ) in fragm006 ,
(aaa [1,1] = 'G' ) in fragm007 ,
(aaa [1,1] = 'H' ) in fragm008 ,
(aaa [1,1] = 'I' ) in fragm009 ,
(aaa [1,1] = 'J' ) in fragm010 ,
(aaa [1,1] = 'K' ) in fragm011 ,
(aaa [1,1] = 'L' ) in fragm012 ,
(aaa [1,1] = 'M' ) in fragm013 ,
(aaa [1,1] = 'N' ) in fragm014 ,
(aaa [1,1] = 'O' ) in fragm015 ,
(aaa [1,1] IN ('P' ,'Q' )) in fragm016 ,
(aaa [1,1] = 'R' ) in fragm017 ,
(aaa [1,1] = 'S' ) in fragm018 ,
(aaa [1,1] = 'T' ) in fragm019 ,
(aaa [1,1] = 'U' ) in fragm020 ,
(aaa [1,1] = 'V' ) in fragm021 ,
(aaa [1,1] = 'W' ) in fragm022 ,
(aaa [1,1] = 'X' ) in fragm023 ,
(aaa [1,1] >= 'Y' ) in fragm024 ;
create index "informix".ix_x_bbb on "informix".xxx (bbb)
fragment by expression
((MONTH (bbb ) = 1 ) AND (DAY (bbb ) <= 15 )
) in fragm001 ,
((MONTH (bbb ) = 1 ) AND (DAY (bbb ) > 15 ) )
in fragm002 ,
((MONTH (bbb ) = 2 ) AND (DAY (bbb ) <= 15 )
) in fragm003 ,
((MONTH (bbb ) = 2 ) AND (DAY (bbb ) > 15 ) )
in fragm004 ,
((MONTH (bbb ) = 3 ) AND (DAY (bbb ) <= 15 )
) in fragm005 ,
((MONTH (bbb ) = 3 ) AND (DAY (bbb ) > 15 ) )
in fragm006 ,
((MONTH (bbb ) = 4 ) AND (DAY (bbb ) <= 15 )
) in fragm007 ,
((MONTH (bbb ) = 4 ) AND (DAY (bbb ) > 15 ) )
in fragm008 ,
((MONTH (bbb ) = 5 ) AND (DAY (bbb ) <= 15 )
) in fragm009 ,
((MONTH (bbb ) = 5 ) AND (DAY (bbb ) > 15 ) )
in fragm010 ,
((MONTH (bbb ) = 6 ) AND (DAY (bbb ) <= 15 )
) in fragm011 ,
((MONTH (bbb ) = 6 ) AND (DAY (bbb ) > 15 ) )
in fragm012 ,
((MONTH (bbb ) = 7 ) AND (DAY (bbb ) <= 15 )
) in fragm013 ,
((MONTH (bbb ) = 7 ) AND (DAY (bbb ) > 15 ) )
in fragm014 ,
((MONTH (bbb ) = 8 ) AND (DAY (bbb ) <= 15 )
) in fragm015 ,
((MONTH (bbb ) = 8 ) AND (DAY (bbb ) > 15 ) )
in fragm016 ,
((MONTH (bbb ) = 9 ) AND (DAY (bbb ) <= 15 )
) in fragm017 ,
((MONTH (bbb ) = 9 ) AND (DAY (bbb ) > 15 ) )
in fragm018 ,
((MONTH (bbb ) = 10 ) AND (DAY (bbb ) <= 15 )@
Great! The obfuscation is fine, I want to see structure not detail. Here
are my recommended commands and I've included some comments below with your
comments.
UPDATE STATISTICS MEDIUM FOR xxx( ccc, eee, fff, ggg, hhh, iii, jjj, kkk,lll ); -- MEDIUM on all non-leading columns in one command
UPDATE STATISTICS HIGH FOR xxx( aaa, bbb, ddd ); -- HIGH on all leadingcolumns in one command
UPDATE STATISTICS LOW FOR xxx( aaa ); -- LOW for each full index key
UPDATE STATISTICS LOW FOR xxx( bbb );
UPDATE STATISTICS LOW FOR xxx( ddd );
UPDATE STATISTICS LOW FOR xxx( aaa, bbb, ccc );
Again, see below for some notes:
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Fri, Aug 21, 2009 at 6:51 AM, HOWARD ANDERSON <
howard.anderson@edl.uk.eds.com> wrote:
> Art,
>
> Many thanks for the prompt reply,
>
> I enclose a snippet of the schema dump for this table, showing the index
> definitions.
> As you can see a primary key is defined on multiple columns, but I know of
> no
> other 'hidden' index constraints.
>
> Thanks for the link to John's paper, I had already read this, and was
> running
> some tests using the PDQ environment variables, but I did not see much (if
> any) gain. Possibly this is due to insufficent available physicial memory
> on
> the system. 'Set explain' showed that the sort data was 32Gb for HIGH, and
> 41Mb was available when PDQ was enabled. I only have 4Gb of physical memory
> installed, and much of this has already been devoured by the database. I
> also
> only have 2 processors in this system, so PSORT_NPROCS is only set to 2. I
> am
> going to set DBUPSPACE=0:50:0 as this gives 50Mb.
You should be able to set PSORT_NPROCS to 2x NUMCPUVPS. Remember that these
sort threads run inside the CPU VPS and as sort threads, they will
frequently be waiting for IO. By running more sort threads than there are
CPU VPs you will increase the parallelization of the sort processing
significantly and reduce runtime. Note that PSORT_NPROCS and PSORT_DBTEMP
are only effective when parallel sorting is possible which requires
PDQPRIORITY > 0. Since you have at least 10 and as many as 24 fragments on
this table and its indexes, setting PDQPRIORITY as high as you can safely
set it during maintenance window processing will dramatically improve
runtimes since the engine will sort each fragment independently and
PDQPRIORITY controls the percentage of memory and CPU VP resources the
selection and sorting processes can get.
Remember also that DBUPSPACE is ONLY affective when PDQPRIORITY is zero,
when PDQPRIORITY > 0 the MGM memory parameters in the ONCONFIG file control
how much memory is available for sorting (for example though the default for
DBUPSPACE is 15MB, you were getting 41MB of in-memory sort space with
PDQPRIORITY set). You may want to increase the MGM memory available for
sorting a bit since you are currently only keeping about 0.11% of your data
in memory at a time requiring between 800 and 1600 merge runs which is a lot
of IO. See the Administrator's Reference and the Performance Guide to see
how the engine calculates the amount of memory it will allow to be used by a
single sort thread.
>
>
> Chuck Berghoff also mentioned use of PDQ to utilise the Memory Grant
> Manager,
> and mentions PSORT_DBSPACE, which I have not yet tried, as I have
> DBSPACETEMP> set in onconfig, however it may be worth trying PSORT_DBSPACE to override
> this, just for the update stats. I am using a Compaq ES40 Alphaserver and
> this
> would seem comparable to his Sun V490, however he is using V10.00.FC5, and
> this may be giving an improvement. (I cannot upgrade on my current
> hardware.)
Using PSORT_DBSPACE can improve sorts and reduce the impact on the engine
considerably. The sort-work files are rarely around long enough for the
UNIX buffer cache routines to actually write them to disk, so this becomes
in-memory sorting effectively as long as you have enough memory - yes I note
that you do not have enough on these systems, but.... The DBSPACETEMP
spaces on the other hand are constrained by IDS's caching policy which
forces all dirty pages to disk at every checkpoint, so there will be more
physical IO involved. You should use at least three and as many as six
filesystems in PSORT_DBTEMP for best performance.
>
>
> I will download a copy of your dostats utility and give it a try.
>
> A colleauge on an internal company forum pointed out to me that each week
> only
> a very small percentage of the data in the table has changed, (1bn to
> 1.001bn)
> so a weekly frequency for update sats on this table is too high. He has
He's probably right, but more important than how much of the data changes
weekly is in what way does the data change. For example, ddd is a serial
column so its values are monotonically increasing and the stats on that
column will not reflect those changes. The engine thinks that there are no
values greater than the highest one that was current at the time of the
stats. The engine knows how serials work, so for a while that's OK, but
when the percentage of new rows increases enough, queries against those rows
may choose the wrong index.
The other leading columns, aaa & bbb, may be more stable. If the new values
being added tend to be distributed in similar percentages as existing values
then the data distributions you have for them will be perfectly effective
for the optimizer to base decisions on for quite a while before you would
see a performance degradation. If this is the case, you could run the full
suite quarterly and only redo the HIGH on ddd only (without the
DISTRIBUTIONS ONLY clause so that the LOW isn't needed) on a weekly basis.
I would also do a LOW on the whole table just to keep the page and row
counts current. This is important especially if there are other tables with
large numbers of rows so that the table ordering in the query plans remains
optimal when joining. If this is the only huge table, you can do the whole
table LOW less frequently.
>
> suggested that a biannual frequency may be more appropriate. My only
> concern
> with this is that it could still impact performance at that point. The root
> problem is the performance impact on insertion. Is it possible to 'nice'
> the
> update stats process in some way ? (Reduce its use of system resorces, at
> the
> expense of time taken.)
>
> Would setting a 'DIRTY READ' isolation level have any performance benefit ?
> Or
> would this just produce inaccurate stats ?
Isolation has no effect on update statistics processing. If it misses or
picks up a couple of inconsistent rows, it doesn't materially affect the
optimizer.
>
>
> Schema dump:
> (I have obfuscated the folowing somewhat, as I don't want to expose the
> real
> table or column names.)
>@
John Miller pointed out to me that because of the way LOW works, it is sufficient to run a single LOW on (aaa, bbb, ccc, ddd) and the engine will properly maintain the stats in all of the indexes' sysindex records. Also, the HIGH should contain a DISTRIBUTIONS ONLY clause. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Aug 21, 2009 at 6:51 AM, HOWARD ANDERSON < howard.anderson@edl.uk.eds.com> wrote: > Art, > > Many thanks for the prompt reply, > > I enclose a snippet of the schema dump for this table, showing the index > definitions. > <SNIP> > --0023545bd6580519af0471a96c27
Hi, Thanks for detailed answer. But still I am a bit confused that; Why do we need to run update statistics low for column "aaa" when we alreayd hve run "high update stats" on this column? As Art said that if PDQPRIORITY is set, PSORT_NPROCS & DBSPACETEMP are no more effective. So if we have large amount of free memory what is better to improve "update stats performance", should we use only the PDQPRIORITY and ignore the use of PSORT_NPROCS & DBSPACETEMP? regards, Kamran
If you have the memory, then increase the MGM memory available for sorting in memory so that almost all sorts will be done completely in memory, set PDQPRIORITY to 10, set PSORT_NPROCS to 2x the number of CPU VPS. Just in case some larger tables' sorts still do not fit in memory, I would still configure PSORT_DBTEMP to list at least three filesystems (preferably on separate structures) to speed sort-work file processing when it is needed. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Aug 24, 2009 at 10:27 AM, KAMRAN HAQ <khaq@i2cinc.com> wrote: > Hi, > Thanks for detailed answer. But still I am a bit confused that; > Why do we need to run update statistics low for column "aaa" when we > alreayd > hve run "high update stats" on this column? > As Art said that if PDQPRIORITY is set, PSORT_NPROCS & DBSPACETEMP are no > more > effective. So if we have large amount of free memory what is better to > improve > "update stats performance", should we use only the PDQPRIORITY and ignore > the > use of PSORT_NPROCS & DBSPACETEMP? > regards, > Kamran > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747864499d5330471e4218c