IDS Forum

Re: UPDATE STATISTICS Performace Improvement

Posted By: Art Kagel
Date: Tuesday, 2 June 2009, at 12:26 p.m.

In Response To: Re: UPDATE STATISTICS Performace Improvement (KAMRAN HAQ)

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
> =======
>
> 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
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001636c5b8effb99b3046b6001b1

Messages In This Thread

• UPDATE STATISTICS Performace Improvement
  ANEES AHMAD -- Thursday, 28 May 2009, at 8:20 a.m.
  • Re: UPDATE STATISTICS Performace Improvement
    Art Kagel -- Thursday, 28 May 2009, at 9:44 a.m.
  • Re: UPDATE STATISTICS Performace Improvement
    Khaled Bentebal -- Thursday, 28 May 2009, at 12:16 p.m.
  • Re: UPDATE STATISTICS Performace Improvement
    jak aka -- Friday, 29 May 2009, at 3:14 a.m.
    • Re: UPDATE STATISTICS Performace Improvement
      KAMRAN HAQ -- Tuesday, 2 June 2009, at 10:49 a.m.
      • Re: UPDATE STATISTICS Performace Improvement
        Art Kagel -- Tuesday, 2 June 2009, at 12:26 p.m.
        • Re: UPDATE STATISTICS Performace Improvement
          KAMRAN HAQ -- Wednesday, 3 June 2009, at 11:15 a.m.
          • Re: UPDATE STATISTICS Performace Improvement
            Art Kagel -- Wednesday, 3 June 2009, at 11:55 a.m.
        • RE: UPDATE STATISTICS Performace Improvement
          LARRY SORENSEN -- Tuesday, 9 June 2009, at 1:10 p.m.
          • Re: UPDATE STATISTICS Performace Improvement
            Richard Kofler -- Tuesday, 9 June 2009, at 1:26 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.