Results 1 to 4 of 4

Thread: Runstats

  1. #1
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170

    Unanswered: Runstats

    What is the importance of the parameters
    NUM_FREQVALUES,NUM_QUANTILES of runstats command. These two parameters can alos be set at the DB level.

    The default valuse is set at 10 and 20 respectively. How can we influence the optimizer by these parameters.

    Has anyone poked around with these parameters !!!!!

  2. #2
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    Both of these parameters can go upto 32767
    Documentation says it will require additional cpu and memory resources if these params are increased.

    I was wondering how these parameters help to improove the performance of a SQL

  3. #3
    Join Date
    Jan 2003
    Posts
    4,304
    Provided Answers: 5
    The statistics that is collected based on these values can show the distribution of the actual data. This can influence the optimizer greatly. Suppose you have a table with a column that has 3 possible values (A,B,C). Without statistics, DB2 will a uniform distribution. But if the distribution is like A=90%, B=8%, and C=2%, then a tablescan maybe faster than an indexscan when a predicate has "where col='A' ".

    Andy

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ARWinner
    The statistics that is collected based on these values can show the distribution of the actual data. This can influence the optimizer greatly. Suppose you have a table with a column that has 3 possible values (A,B,C). Without statistics, DB2 will a uniform distribution. But if the distribution is like A=90%, B=8%, and C=2%, then a tablescan maybe faster than an indexscan when a predicate has "where col='A' ".

    Andy
    In the above example, you only need to collect distribution stats on 3 values. So it depends on whether you have highly skewed data, and how many values there are of the column. If there are 32767 different values, then DB2 will likely use the index anyway, even if the data is not skewed, so you definitely do not want to capture that many values.

    If you capture the average number of rows that exist on a page (depends on the row size and page size) that should be plenty. The reason for that is that DB2 will avoid using an index if it assumes that it has to read every page, and if there are more values than there are rows on a page, then DB2 is much more likely to use an index even if the data is not skewed.

    In order to make it work, you should use the following runstats command:

    runstats on table <table-name> with distribution on key columns and indexes all;

    Generally, it only makes sense to capture distribution on key (index) columns, since a skewed distribution typcially affects whether or not an index is used by DB2. Capturing too many stats makes the catalog much larger, and slows down package compilation time.
    Last edited by Marcus_A; 05-27-08 at 15:52.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •