IDS Forum

Re: about update statistics performance ?

Posted By: Art Kagel
Date: Thursday, 2 April 2009, at 3:30 p.m.

In Response To: about update statistics performance ? (VARAPORN VERAVITHAYA)

None of these is strictly correct according to the Performance Guide and to
John Miller III's white paper on the subject. Obviously any three of the
sets of commands that you present below will generate distributions and the
server's performance for most queries will be identical after each set. The
difference between these and the recommendations is that the recommended set
of commands will produce useful distributions for the table at minimal cost
in time to run the update statistics commands.

These are the recommended commands and are indeed the ones that will be
generated either by my dostats utility or by the IDS 11 automated update
statistics feature, when there are no distributions already present, if you
enable it:

update statistics medium for table t1 (<list of all columns of t1 not listed
below in HIGH command>);

update statistics high for table t1( a, f, c, e ) distributions only;

update statistics low for table t1(a, b, c, d, x1);
update statistics low for table t1(a, b, e, f, x2);
update statistics low for table t1(f, x3, x4, x5);

For some older engines, breaking the commands up differently is more
efficient, but for engines since 7.31xD3 and 9.30xC2 this has been the
recommendation. Dostats detects the engine version and modifies the set of
commands that it generates depending on the version (though you can
optionally override that optimization because on some smaller systems with
limited memory the older method is a bit faster).

Anyway, these commands will first generate MEDIUM level distributions for
all columns that do not head an index and are not the first different column
between multiple indexes that begin with the same set of columns. Then it
generates HIGH level distributions for the columns that either head an index
(a & f) or are the first different column in some index (c & e). This is
done in a single command to allow the engine to scan the table only once to
gather the data for all four columns (similarly the MEDIUM columns in the
first command should be listed in a single command or at least in as few
commands as possible). The HIGH is run without DISTRIBUTIONS ONLY because
the low level stats for these columns will be gathered in the last step
anyway. Lastly a LOW is run for each whole index key one key in each
command. This gathers low level stats at the table level (# rows & number
of data pages), column level (2nd highest and 2nd lowest value), and at the
index level (#nodes, #leaves, depth, & width).

Please note that all of these decisions can be automated by using my dostats
utility which part of the package utils2_ak and is downloadable either from
the IIUG Software Repository (www.iiug.org/software) or from the Oninit web
site (www.oninit.com/utils). You can also use the 11.xx Automated Update
Statistics feature to produce and run stats commands and to schedule those
runs.

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, Apr 2, 2009 at 4:50 AM, VARAPORN VERAVITHAYA
<varaporn.v@cdg.co.th>wrote:

> Hi ,
>
> I have somw questions about update statistics from AnswerOnline 11.5 as
> following :
>
> Here from IDS11.5 :
>
> Example of UPDATE STATISTICS HIGH statements for the first column in each
> index that differs:
>
> For example, suppose you have the following indexes on table t1:
>
> CREATE INDEX ix_1 ON t1 (a, b, c, d, x1) ...
> CREATE INDEX ix_2 ON t1 (a, b, e, f, x2) ...
> CREATE INDEX ix_3 ON t1 (f, x3, x4, x5) ...
>
> Step 3 executes UPDATE STATISTICS HIGH on column a and column f.
>
> Then run UPDATE STATISTICS HIGH on columns c and e.
> UPDATE STATISTICS HIGH FOR TABLE t1(c);
> UPDATE STATISTICS HIGH FOR TABLE t1(e);
>
> In addition, you can run UPDATE STATISTICS HIGH on column b, although this
> is
> usually not necessary. Suppose you have indexes that begin with the same
> subset
>
> >From table above, I generated three commands.
> 1.
>
> UPDATE STATISTICS HIGH FOR TABLE t1 (a,b,c,d,x1);
>
> UPDATE STATISTICS HIGH FOR TABLE t1 (a,b,e,f,x2);
>
> UPDATE STATISTICS MEDIUM FOR TABLE t1 (f,x3,x4,x5);
>
> 2.
>
> UPDATE STATISTICS HIGH FOR TABLE t1 (a,b);
>
> UPDATE STATISTICS HIGH FOR TABLE t1 (c);
>
> UPDATE STATISTICS HIGH FOR TABLE t1 (e);
>
> UPDATE STATISTICS HIGH FOR TABLE t1 (f);
>
> UPDATE STATISTICS MEDIUM FOR TABLE t1(x3,x4,x5);
>
> UPDATE STATISTICS MEDIUM FOR TABLE t1(x2);
>
> UPDATE STATISTICS MEDIUM FOR TABLE t1(d,x1);
>
> 3.
>
> UPDATE STATISTICS HIGH FOR TABLE t1 (a);
>
> UPDATE STATISTICS HIGH FOR TABLE t1 (c);
>
> UPDATE STATISTICS HIGH FOR TABLE t1 (e);
>
> UPDATE STATISTICS HIGH FOR TABLE t1 (f);
>
> UPDATE STATISTICS MEDIUM FOR TABLE t1(d);
>
> UPDATE STATISTICS MEDIUM FOR TABLE t1(x1);
>
> UPDATE STATISTICS MEDIUM FOR TABLE t1(x2);
>
> UPDATE STATISTICS MEDIUM FOR TABLE t1(x3);
>
> UPDATE STATISTICS MEDIUM FOR TABLE t1(x4);
>
> UPDATE STATISTICS MEDIUM FOR TABLE t1(x5);
>
> Questions :
> 1. Is it correct 1-3 ?
> 2. What is the differences between 1-3 in term of data distribution ?
> 3. Any other recommend ?
>
> Thank You ,
> Varaporn :-))
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--0016368e20f67437a504669776ed

Messages In This Thread

• about update statistics performance ?
  VARAPORN VERAVITHAYA -- Thursday, 2 April 2009, at 4:49 a.m.
  • Re: about update statistics performance ?
    Frank Langelage -- Thursday, 2 April 2009, at 6:27 a.m.
  • Re: about update statistics performance ?
    VIKAS HIVARKAR -- Thursday, 2 April 2009, at 7:09 a.m.
  • Re: about update statistics performance ?
    Art Kagel -- Thursday, 2 April 2009, at 3:30 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.