IDS Forum

Re: Update Statistics

Posted By: Art Kagel
Date: Wednesday, 3 March 2010, at 12:12 p.m.

In Response To: Update Statistics (Wyza, Jonathon)

Take the suggestions to read the Performance Guide and the two white papers
someone already posted, but here's a quick and dirty tutorial:

- The optimizer needs statistical data on the contents of tables to

decide on the best query path. Which table to select first, which to join

to next, etc. whether to use an index (and if so which one) or sequential

scan or to scan a table into a hash table to speed searches or even whether

to create a temporary index.

- At the base level, UPDATE STATISTICS LOW provides each table's row

count, the second smallest and second largest value for each column in the

table, and the size and depth of each index's B+Tree. This is sufficient to

allow the optimizer to choose between indexes and to perform gross table

ordering. This usually results in reasonable performance for most simple

queries involving fewer than 4 tables. The optimizer, however, doesn't

have enough information to change the query plan depending on specific

filter values included in the query. It's a "one size fits all" strategy.

At this level, the optimizer tends to choose an index key matching an ORDER

BY clause because it can't determine if the sort would be faster because it

cannot correctly estimate the number of rows to be returned.

- UPDATE STATISTICS MEDIUM provides data distributions for those columns

against which it is run using statistical sampling. By default only a small

number of rows are sampled. This mainly improves the table ordering in more

complex queries but does not normally permit the optimizer to correctly

choose one index over another or to choose an index for filter or join

efficiency rather than return order efficiency. The optimizer's estimate of

the number of rows returned is better than under LOW only, but not perfect.

The new SAMPLING option can improve the quality of MEDIUM statistics by

increasing the sampling size. Run time is significantly longer than for

LOW.

- UPDATE STATISTICS HIGH provides exact data distribution information for

those columns against which it is run (at least as of the moment it was

started) as it reads and quantifies every row in the table. This can take

much longer than either LOW or MEDIUM statistics but results in the

optimizer making better decisions. The optimizer's estimate of the number

of rows to be returned is rarely off by more than a few. It is normally

only necessary to create HIGH statistics for the lead columns of each index

key and for commonly joined non-indexed columns. Additionally it can be

helpful to generate HIGH statistics for columns used in table and index

fragmentation expressions and if you have two or more compound indexes that

begin with the same sublist of columns it is good to also generate HIGH

statistics for the first column that is different in each index.

- UPDATE STATISTICS FOR PROCEDURE/FUNCTION/ROUTINE recompiles SPL stored

procedures thereby creating a new query plan for the procedure. This is

required after changes to tables that are accessed by the procedure by

ALTERing the table to add, drop, or modify columns or after running UPDATE

STATISTICS on those tables. The engine will automatically recompile the

procedure the first time it is executed after the table has changed (the

change marks the procedure's query plan invalid) but that will add to the

procedure's runtime, so it is good to recompile it manually immediately

after modifying a table that the procedure references.

- Index builds in 11.50 automatically calculate LOW statistics for you

(earlier engines did not do that).

- The IDS Automatic Update Statistics feature (or AUS) uses the IDS task

scheduler to run a set of stored procedures and Java User Defined Functions

(UDFs) to perform this task for you periodically and automatically.

- How often to run update stats? Tough question, YMMV but in general

when there are significant changes to the number of rows in a table or in

the relative percentages of rows in a table with a particular range of

values then you need to recalculate the statistical distributions. AUS and

dostats (see below) figure this out for you.

Finally, as noted by another poster, get my dostats utility, which is in the
package utils2_ak which you can download from the IIUG Software Repository
for free. It implements the protocol of commands as recommended in the
Performance Guide and with the recommendations made by John Miller in his
white paper. Dostats provides many features that AUS does not include.
Dostats can even schedule update statistics runs using the IDS task
scheduler feature just as AUS does, so it can be used as an AUS replacement
if you like its features better.
Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. 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, Mar 3, 2010 at 8:45 AM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote:

> I've seen the recommendation to do update statistics on ids on this list
> over
> the last couple months in certain situations. Sadly I know little about
> administering IDS but I've muddle through so far. I was wondering if
> someone
> could do a quick tutorial about update statistics covering things like when
> to
> use it and why to use it and how often to use it.
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu>
> ==========================
> SLES 10 SP2 & IDS 10.0 HC9
>
> " I would love to change the world, but they won't give me the source
> code."
> -- Unknown
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--000e0ce00a84f889f30480e894fb

Messages In This Thread

• Update Statistics
  Wyza, Jonathon -- Wednesday, 3 March 2010, at 8:45 a.m.
  • Re: Update Statistics
    Fernando Nunes -- Wednesday, 3 March 2010, at 9:50 a.m.
    • RE: Update Statistics
      Wyza, Jonathon -- Wednesday, 3 March 2010, at 10:14 a.m.
      • Re: Update Statistics
        Art Kagel -- Wednesday, 3 March 2010, at 2:24 p.m.
      • RE: Update Statistics
        John Miller iii -- Wednesday, 3 March 2010, at 6:49 p.m.
  • Re: Update Statistics
    Art Kagel -- Wednesday, 3 March 2010, at 12:12 p.m.
  • Re: Update Statistics
    VIKAS HIVARKAR -- Friday, 5 March 2010, at 10:47 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.