IDS Forum

Re: Update Stats Time

Posted By: Art S. Kagel (Oninit LLC)
Date: Tuesday, 15 January 2008, at 3:58 p.m.

In Response To: Re: Update Stats Time (DAVE HAYDU)

DAVE HAYDU wrote:
> Art, we are currently using IDS 7.31UD3. I have the PSORT_NPROCS variable set
> to 3. We are currently running stats by table and our script generates
> multiple sql scripts so the tables are done in parallel. I was following with
> the recommendations in the performance guide and working with a script
>

So far so good. However, you are using 7.31UD3 which is an early
version which includes several optimizations to how the data
distributions are gathered. The result of that optimization is that if
you take advantage of those new features, by executing fewer statements
containing more columns in each, the total runtime will be reduced. See
John Miller III's white paper:

http://www.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/0203miller.html

Dostats is an ESQL/C application I wrote that implements all of the
recommendations in the Performance Guide and in John's paper, with
several options, including the two pairs of options I mentioned, -a/-A
and -b/-B. Dostats detects the server version and adjusts its
operations depending on whether you are running it against an optimized
version of the server or not (details in John's paper, but you are
running an optimized version). On an optimized server John's
recommendations are triggered and dostats builds massive update
statistics statements up to 64K in length (the maximum IDS will allow)
containing a list of all columns that require updating at each level for
each table. (There's even an option to reverse the behavior of the
version detection so you can test which set of output runs better on
your particular system as some smaller systems still work better using
the older algorithm.)

Here's a description of the dostats options I mentioned. Using them
you'll minimize the number of tables actually updated each night.

-a I call the aging option. It selects tables to update ONLY if their
existing data distributions are at least <N> day old, you can select the
aging timeframe with the -A option by looking at the sysdistrib table
entry for every column in the table and comparing today to the oldest
such record.

-b I call the browsing option. It selects tables to update ONLY if the
number of rows in the table has changed (up or down) by more than a
specified percentage of the table's size the last time stats were
updated to at least LOW levels. The -B options allows you to set the
detection threshold. This is done, if you want to write your own
version, by comparing the nrows column in systables (which is only
maintained by UPDATE STATISTICS LOW and HIGH) with that stored in the
table's partition header page (sysmaster:sysptnhdr.nrows) which is
always up-to-date.

The browsing and aging options can work together in an OR fashion to
select tables that satisfy either criterion.

At my former employer they typically run:

dostats -d mydatabase -a -A 7 -d -D 15
daily so that every table is updates at least once a week and more
frequently if they are more volatile. They also have a larger window on
weekends, so they run certain databases without the -a/-A or -b/-B
options on the weekend to reduce the load during the week even further.
So all tables in those databases get updated on the weekend with
volatile tables being updated again sometime during the week. Less
critical databases are run only during the week with the aging and
browse options while low volatility database are only run on the weekend
or monthly without them.

My drive dostats script works with dostats and it's -i @table_list_file
feature to run <M> copies of dostats each with a list of tables to update.

Both dostats and drive_dostats are included in the package utils2_ak
which you can download from the IIUG Software Repository.

> provided in our venders application. The flags that you are referencing -a/-A
> or -b/-B are apart of which script? It also appears when I try to do multiple
> database at once I start to run into IO problems as well.
Yes, there is a limit to how many tables you can update in parallel
given your available memory, CPU, and IO resources. Unfortunately,
there's no formula. You have to determine that limit yourself for your
system mostly by trial and error.

Art S. Kagel
Oninit

===========================================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.oninit.com/home/disclaimer.php

===========================================================================================

Messages In This Thread

• Update Stats Time
  DAVE HAYDU -- Tuesday, 15 January 2008, at 1:35 p.m.
  • Re: Update Stats Time
    Art S. Kagel (Oninit LLC) -- Tuesday, 15 January 2008, at 2:57 p.m.
    • Re: Update Stats Time
      DAVE HAYDU -- Tuesday, 15 January 2008, at 3:10 p.m.
      • RE: Update Stats Time
        Murray Wood -- Tuesday, 15 January 2008, at 3:39 p.m.
      • Re: Update Stats Time
        Art S. Kagel (Oninit LLC) -- Tuesday, 15 January 2008, at 3:58 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.