UPDATE STATISTICS Performace Improvement
Asked how to speed up UPDATE STATISTICS on an OLTP system (and avoid sysprocplan locking). Advice given: follow John Miller III's white paper and the Performance Guide's recommended command suite (LOW per index key, HIGH with DISTRIBUTIONS ONLY on leading index columns, MEDIUM on the rest), set PDQPRIORITY/PSORT_NPROCS, size DS_TOTAL_MEMORY, and use PSORT_DBTEMP across several filesystems. Art Kagel explained DBUPSPACE caps sort memory at 50MB (and DS_NONPDQ_QUERY_MEM overrides it in 10.00+), that PDQ memory comes from MGM instead, and recommended his dostats utility (with aging/browse options against sysactptnhdr and sysdistrib) or the built-in Auto Update Statistics, which is enabled by default in 11.x and can be disabled via OAT's scheduler tab.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello, How can I improve the execution performance of 'Update Statistics'?? In our OLTP system we want to avoid any sysprocplan issue and for that purpose we need to execute update statistics as speedily as possible. Regards, Anees Ahmad
Read John Miller III's white paper at this link: * http://www.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/ 0203miller.html * Also, you can use my dostats utility, taking the recommendations in John's paper into consideration about setting PDQPRIORITY and PSORT_NPROCS and allocating enough memory to insure that all sorting is performed in memory. Further I would recommend using PSORT_DBTEMP (which takes precedence over DBSPACETEMP) to let IDS use three to six filesystems for any disk sorts that it has to make as that tends to be faster as it takes advantage of quirks in the system buffer cache to avoid any physical disk writes. Dostats implements the recommended suite of commands that John details in his paper automatically and it will also compile your stored procedures one at a time avoiding most of the locking problems in sysprocplan that one encounters if one just runs UPDATE STATISTICS FOR PROCEDURE; with no procedure name. Dostats has many other options that you can use to keep your data distributions more up to date with less system overhead and interruption (see the -a/-A and -b/-B options for example). Dostats is included in the package utils2_ak which you can download from the Oninit web site (www.oninit.com/utils) or from the IIUG Software Repository ( www.iiug.org/software). 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 Thu, May 28, 2009 at 8:20 AM, ANEES AHMAD <aanees@i2cinc.com> wrote: > Hello, > How can I improve the execution performance of 'Update Statistics'?? > In our OLTP system we want to avoid any sysprocplan issue and for that > purpose > we need to execute update statistics as speedily as possible. > > Regards, > Anees Ahmad > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c923d7644387046af927bb
Hi Anees,
It depends on which UPDATE STATISTICS your are using (Low, medium or high or
a combination of these for different objects of the database).
You can optimize your "UPDATE STATISTICS HIGH or MEDIUM " statement by
setting:
- PDQPRIORITY to a value > than 0 (Max is 100)
- DS_TOTAL_MEMORY= <value important enough for sorting purposes in the
magnitude of 100s of megabytes>; again it depend on the size of tha data
sorted by the update statistics.
DO not forget to check whether you have DS_MAX_QUERIES is not blocking you
and MAXPDQPRIORITY not blocking you. If the number of concurrent queries
going through the PDQ mechanism (check with onstat -g mgm) is reached or
MAXPDQPRIORITY is reached, then
If PDQPRIORITY=0, then DBUPSPACE is used for sorting space by UPDATE
STATISTICS.
Again, we need to know more on your database volumes (sizes of your tables)
and which UPDATE STATISTICS options your using..
If you want to avoid locks on the sysprocplan system table and your database
is huge, you can just perform UPDATE STATISTICS for the tables and columns
involved in the stored procedure or stored procedures involved.
Again, we need more details to help you properly.
Khaled Bentebal
Email: khaled.bentebal@consult-ix.fr
----- Original Message -----
From: "ANEES AHMAD" <aanees@i2cinc.com>
To: <ids@iiug.org>
Sent: Thursday, May 28, 2009 2:20 PM
Subject: UPDATE STATISTICS Performace Improvement [15848]
> Hello,
> How can I improve the execution performance of 'Update Statistics'??
> In our OLTP system we want to avoid any sysprocplan issue and for that
> purpose
> we need to execute update statistics as speedily as possible.
>
> Regards,
> Anees Ahmad
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
Hi,
Please try
- Use PDQPRIORITY (10 is ok)
- Change the pattern of update stats statement. For example:
update statistics MEDIUM for table A;
update statistics HIGH for table A(col1,col2);
update statistics HIGH for table A(col1,col3);
update statistics HIGH for table A(col5,col7);
Change to :
set PDQPRIORITY 10;
update statistics MEDIUM for table A;
update statistics HIGH for table A(col1,col2,col3,col5,col7);
- If above way still taking long time as you expect, try to decrease the
degree from HIGH to MEDIUM with appropriated resolution and confidence level
- Don't run too much parallel (table) for update statistics.
From my experience; DBUPSPACE and the others parameter not help the update
stats faster. Anyway, you can try it.
Jakkrit A.
________________________________
From: ANEES AHMAD <aanees@i2cinc.com>
To: ids@iiug.org
Sent: Thursday, May 28, 2009 7:20:05 PM
Subject: UPDATE STATISTICS Performace Improvement [15848]
Hello,
How can I improve the execution performance of 'Update Statistics'??
In our OLTP system we want to avoid any sysprocplan issue and for that purpose
we need to execute update statistics as speedily as possible.
Regards,
Anees Ahmad
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
We are seeking for an efficient way to update stats for tables.
The real problem is to gather stats for large high transaction tables.
We ran following test cases on our test enviornment and trying to know:
Is the maximum memory granted for update-stats(sort) is 50 MB if use DBUPSPACE
(enviornment variable)
as we got 50 MB in output even trying to use more than 50 MB i.e.,
DBUPSPACE=102400:100 ?
Why the memory allocated for sort is same whether we used DBUPSPACE or not ?
Why "Sort data" is increaed when we use PSORT_NPROCS ?
What is relationship between PSORT_NPROCS and DBSPACETEMP ?
What is suggested way to run medium update stats on columns of a large high
traansaction table and what would be appropriate values for resolution and
confidence ?
Case 1
=======
Script run
-----------
set explain on;
update statistics low for table orders drop distributions;
update statistics medium for table orders resolution 2.00000 0.95000;
UPDATE STATISTICS OUTPUT:
-------------------------
Table: inv.orders
Mode: MEDIUM
Number of Bins: 67 Bin size 92
Sort data 88.1 MB Sort memory granted 50.0 MB
Estimated number of table scans 2
Scan 207 Sort 1 Build 1 Insert 0 Close 0 Total 209
Completed pass 1 in 3 minutes 29 seconds
Scan 5 Sort 0 Build 1 Insert 0 Close 0 Total 6
Completed pass 2 in 0 minutes 6 seconds
########################################################
Case 2
=======
Enviornment variable set:
DBUPSPACE=102400:100 ;export DBUPSPACE
Script run
-----------
set explain on;
update statistics low for table orders drop distributions;
update statistics medium for table orders resolution 2.00000 0.95000;
UPDATE STATISTICS OUTPUT:
-------------------------
Table: inv.orders
Mode: MEDIUM
Number of Bins: 67 Bin size 92
Sort data 88.1 MB Sort memory granted 50.0 MB
Estimated number of table scans 2
Scan 24 Sort 1 Build 2 Insert 0 Close 0 Total 27
Completed pass 1 in 0 minutes 27 seconds
Scan 2 Sort 1 Build 0 Insert 0 Close 0 Total 3
Completed pass 2 in 0 minutes 3 seconds
########################################################
Case 3
=======
Script run
-----------
set explain on;
set pdqpriority 60;
update statistics low for table orders drop distributions;
update statistics medium for table orders resolution 2.00000 0.95000;
UPDATE STATISTICS OUTPUT:
-------------------------Table: inv.orders
Mode: MEDIUM
Number of Bins: 67 Bin size 92
Sort data 88.1 MB PDQ memory granted 92.6 MB
Estimated number of table scans 1
########################################################
Case 4
=======
Enviornment variable set:
PSORT_NPROCS=2
DBSPACETEMP=tempdbs_test
Script run
-----------
set explain on;
update statistics low for table orders drop distributions;
update statistics medium for table orders resolution 2.00000 0.95000;
UPDATE STATISTICS OUTPUT:
-------------------------
Table: inv.orders
Mode: MEDIUM
Number of Bins: 67 Bin size 92
Sort data 168.9 MB Sort memory granted 50.0 MB
Estimated number of table scans 4
Scan 16 Sort 0 Build 1 Insert 0 Close 0 Total 17
Completed pass 1 in 0 minutes 17 seconds
Scan 2 Sort 0 Build 1 Insert 0 Close 0 Total 3
Completed pass 2 in 0 minutes 3 seconds
Scan 2 Sort 0 Build 1 Insert 0 Close 0 Total 3
Completed pass 3 in 0 minutes 3 seconds
Scan 2 Sort 0 Build 0 Insert 0 Close 0 Total 2
Completed pass 4 in 0 minutes 2 seconds
########################################################
Case 5
=======
Enviornment variable set:
PSORT_NPROCS=2
DBSPACETEMP=tempdbs_test
Script run
-----------
set explain on;
set pdqpriority 60;
update statistics low for table orders drop distributions;
update statistics medium for table orders resolution 2.00000 0.95000;
UPDATE STATISTICS OUTPUT:
-------------------------
Table: inv.orders
Mode: MEDIUM
Number of Bins: 67 Bin size 92
Sort data 168.9 MB PDQ memory granted 100.0 MB
Estimated number of table scans 2
Scan 19 Sort 0 Build 1 Insert 1 Close 0 Total 21
Completed pass 1 in 0 minutes 21 seconds
Scan 2 Sort 1 Build 0 Insert 0 Close 0 Total 3
Completed pass 2 in 0 minutes 3 seconds
########################################################
regards,
Kamran
First what engine version are you running? How much memory is on the
machine? Do you have three or more independent filesystems that you can use
for temporary sort-work files?
Answers to you specific questions below:
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 Tue, Jun 2, 2009 at 10:49 AM, KAMRAN HAQ <khaq@i2cinc.com> wrote:
> We are seeking for an efficient way to update stats for tables.
> The real problem is to gather stats for large high transaction tables.
> We ran following test cases on our test enviornment and trying to know:
>
> Is the maximum memory granted for update-stats(sort) is 50 MB if use
> DBUPSPACE
> (enviornment variable)
> as we got 50 MB in output even trying to use more than 50 MB i.e.,
> DBUPSPACE=102400:100 ?
In the later releases of IDS 7.31 and 9.30 (after xD2 and xC2 respectively)
the maximum memory that DBUPSPACE can use was increased from 15MB to 50MB
and the default sort memory from 4MB to 15MB. DBUPSPACE is used for
non-parallel sorting when PDQPRIORITY is zero and PSORT_NPROCS is not set.
In IDS 10.00 and later there is also the ONCONFIG parameter
DS_NONPDQ_QUERY_MEM which overrides DBUPSPACE and defaults to only 128K
(range 128K to 25% of DS_TOTAL_MEMORY).
When PDQPRIORITY is non-zero and PSORT_NPROCS is set greater than one (1)
then MGM memory is used for sort-work space in memory and that is governed
by DS_TOTAL_MEMORY.
PSORT_NPROCS controls into how many parallel sort threads a sort task is
divided. So, the higher you set this, the more threads are sorting and each
sort thread gets the maximum sort memory that is configured as indicated
above.
>
>
> Why the memory allocated for sort is same whether we used DBUPSPACE or not
> ?
It isn't, but if you try to set DBUPSPACE to something greater than 50MB it
reverts to 50MB.
>
>
> Why "Sort data" is increaed when we use PSORT_NPROCS ?
Because each of the parallel sort threads gets allocated the maximum sort
memory if needed.
>
>
> What is relationship between PSORT_NPROCS and DBSPACETEMP ?
By default all sorts use any non-logged (ie temp) dbspaces configured into
DBSPACETEMP to store any sort-work files that it cannot keep in memory when
the sorted data is larger than the maximum sort memory allocated to it.
That goes for simple sorts and parallel sorts. However, you can move the
sort-work files to filesystem space relieving some of the burden on the
engine and taking advantage of the system buffer cache if you also set
PSORT_DBTEMP to a list of three or more (more than 6 does not seem to
improve performance I have found) filesystems that are on different physical
structures the parallel sorts will use those filesystems to store temporary
sort-work files instead of the temp dbspaces in DBSPACETEMP. This can
improve sort speed for sorts that have to go to disk if the system buffer
cache is large enough.
>
>
> What is suggested way to run medium update stats on columns of a large high
> traansaction table and what would be appropriate values for resolution and
> confidence ?
None of the methods below. You should definitely be using the recommended
suite of commands that are listed in the Performance Guide. If you are
using a newer engine version (you should always post your engine version and
platform information when posting to this forum BTW), which is 7.31xD2 or
9.30xC2 or later, the command suite that John Miller III recommends in his
White Paper is often faster than the original recommendations and produces
the same level and quality of data distributions. That latter
recommendation is:
- LOW for each entire index key independently
- DROP DISTRIBUTIONS is not neccessary except the first time this is run
after a server version upgrade in-place
- HIGH on any column that is the first column in some index with the
DISTRIBUTIONS ONLY clause included
- Also do HIGH for some non-leading index columns of more than one
index begins wuth the same subset of columns. In that case also
do HIGH on
the first column of each index that is different from any of the others.
- Gather all HIGH columns into as few UPDATE STATISTICS HIGH commands
as possible - each can be up to 64K in length
- MEDIUM on any columns not included in the HIGH commands
Example:
table:
CREATE TABLE person_addresses (
person_id INTEGER NOT NULL,
sequence SERIAL(2) NOT NULL,
address_type INTEGER,
address_line VARCHAR(255,0),
city CHAR(40),
state CHAR(3),
country CHAR(3)
DEFAULT 'US',
postal_code CHAR(20),
comments VARCHAR(100,0)
) ;
CREATE UNIQUE INDEX person_addresses_ak1 ON person_addresses ( person_id,
address_type, sequence );
CREATE INDEX person_addresses_fk1 ON person_addresses ( person_id );
CREATE INDEX person_addresses_fk2 ON person_addresses ( address_type );
CREATE UNIQUE INDEX person_addresses_pk ON person_addresses ( person_id ,sequence );
The optimal set of commands for this table would be:
UPDATE STATISTICS LOW FOR TABLE person_addresses (person_id, sequence);
UPDATE STATISTICS LOW FOR TABLE person_addresses (person_id, address_type,sequence);
UPDATE STATISTICS LOW FOR TABLE person_addresses (address_type);
UPDATE STATISTICS LOW FOR TABLE person_addresses (person_id);
UPDATE STATISTICS HIGH FOR TABLE person_addresses (person_id, sequence,address_type) DISTRIBUTIONS ONLY;
UPDATE STATISTICS MEDIUM FOR TABLE person_addresses (address_line, city,
state, country, postal_code, comments, ifx_insert_checksum,ifx_row_version);
This is the recommended set of commands and is the exact algorithm that is
implemented in my dostats utility and also, as closely as the server logic
will allow, in the IDS 11.10+ Auto Update Statistics system.
None of your examples below do not include a HIGH and without the HIGHs on
index leading columns the optimizer will make sub-optimal decisions.
>
>
> Case 1
> =======
>
> Script run
> -----------
> set explain on;
> update statistics low for table orders drop distributions;
> update statistics medium for table orders resolution 2.00000 0.95000;>
> UPDATE STATISTICS OUTPUT:
> ------------------------->
> Table: inv.orders
> Mode: MEDIUM
> Number of Bins: 67 Bin size 92
> Sort data 88.1 MB Sort memory granted 50.0 MB
> Estimated number of table scans 2
>
> Scan 207 Sort 1 Build 1 Insert 0 Close 0 Total 209
> Completed pass 1 in 3 minutes 29 seconds
> Scan 5 Sort 0 Build 1 Insert 0 Close 0 Total 6
> Completed pass 2 in 0 minutes 6 seconds
>
> ########################################################
>
> Case 2
> =======
>@@NL
Thanks for such a nice and detailed answer. Here are answers what you asked. First what engine version are you running? IDS 11.5 FC4 How much memory is on themachine? 16 GB (4 GB free usually) Do you have three or more independent filesystems that you can use for temporary sort-work files? Production servers are connected through SAN How can we identify what tables need update stats. Is there any sysmaster view? regards, Kamran
If you run dostats with the browse options (-b/-B) it looks at each table's partition header page (sysmaster:sysactptnhdr) for the actual number of rows versus the number recorded during the last update statistics run in systables. If those values differ by more than a specified percentage then new distributions are generated for that table. If you run dostats with the aging options (-a/-A) it looks at the age of each table's data distributions (<database>:sysdistrib). If that date is more than a specified number of days old then new distributions are generated for that table. What I normally do for my clients (and what I set up when I was at Bloomberg) was to run dostats every night with browsing and aging options enabled to 7 day aging and 15% browsing and on the weekend with no browsing - 7 day aging only. That way all tables have fresh stats calculated at least once a week and any table that has large numbers of rows inserted or deleted will be updated midweek. You can do something similar or you can just run dostats or use the built-in Auto Update Statistics feature so do the same. If you keep statistics on inserts, deletes, and updates (perhaps using a sensor in the 11.50 scheduler) you can also update tables that have had a significant number of updates or even offsetting inserts and deletes - something that dostats can't detect. 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, Jun 3, 2009 at 11:15 AM, KAMRAN HAQ <khaq@i2cinc.com> wrote: > Thanks for such a nice and detailed answer. Here are answers what you > asked. > > First what engine version are you running? > IDS 11.5 FC4 > > How much memory is on themachine? > 16 GB (4 GB free usually) > > Do you have three or more independent filesystems that you can use for > temporary sort-work files? > Production servers are connected through SAN > > How can we identify what tables need update stats. Is there any sysmaster > view? > > regards, > Kamran > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5a4406b996e046b73b0ff
So, do I understand correctly that Automatic Update Statistics (AUS) is enabled by default, and that you must change or delete it if you do not want the default configuration and actions?
LARRY SORENSEN schrieb: > So, do I understand correctly that Automatic Update Statistics (AUS) is > enabled by default, and that you must change or delete it if you do not want > the default configuration and actions? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Hi Larry, at least in the DevEdition I am testing with, this is so. One can easily disable it using OAT in the scheduler tab. One of the few reasons why also me likes OAT, if not the only one... cu, dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g