Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Location
    Nottingham, UK
    Posts
    12

    Unanswered: Index Usage stats

    Hi

    Does anyone know a method of analysing how often a index has been utilitised? We have a couple of tables which have gradually become a little over indexed and we wish to determine which can be eliminated as not being used.

    This is on DB2/UDB 7.2 fp7 on AIX

    Any thoughts?

    Thanks
    Jamie

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It's a little difficult for dynamic SQL statements (but not impossible). You can determine which indexes are used and then determine which are not used.

    For static SQL, any index not attached to a package (as reflected in the DB2 catalog) is not used for SQL statements that are bound (with the bind command) using the DBRM output from the pre-compile of an application program.

    But a good rule of thumb is to eliminate any index (except those defined as clustered) where the number of distinct values is less than the number of rows per page of the table.

    So if the row size of the table is about 200, then there are about 20 data rows per 4K page in the table. Thus any non-clustered index with less than 20 distinct values probably will not be used by DB2.

    This assumes however, that there is a fairly random distribution of distinct values of the index. If the distribution of index values is very highly skewed, then it might good to keep the index in certain cases because it may be used (if detailed table and index statistics are captured during runstats).

    But my experience is that DBA’s and/or developers who create too many indexes, don’t understand clustering, so that needs to be examined at the same time.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Mar 2003
    Posts
    343
    Thus any non-clustered index with less than 20 distinct values probably will not be used by DB2.
    ---- why is this true?
    And doesn't it have some connection with num_quantiles and num_freqvalues?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Yes, if the data is highly skewed (and one captures detailed stats) then it does depend on things like num_quantiles and num_freqvalues.

    But the purpose of using an index is to avoid reading a data page on the table. In order to avoid reading a 4K data page, DB2 would need to be fairly sure that it didn't need any of the rows on that page. If there are 20 rows per page, then the number of unique values of the index should be greater than 20 (if the data is not skewed).

    But here are some common examples of poor indexes:

    - Division code or company code (when there are only a few divisions or companies being used).
    - Sex (male or female) - this may be obvious, but I saw an index on sex once.
    - etc.

    If you have less than 20 distinct values the data needs to be skewed and one needs to search on the values that are not common for DB2 to use the index.

    I am talking about the number of distinct values in the entire index, not just any one column of an index (if the index has multiple columns).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You can use the following query for some basic analysis of index statistics


    SELECT
    indname,
    npages,
    card,
    firstkeycard as firstk,
    first2keycard as f2key,
    first3keycard as f3key,
    first4keycard as f4key,
    fullkeycard as fullkey,
    nleaf,
    nlevels as nlev,
    clusterratio as cr,
    clusterfactor as cf,
    uniquerule as u,
    t.colcount as tbcol,
    i.colcount as ixcol
    FROM syscat.tables t,
    syscat.indexes i
    WHERE i.indschema = t.tabschema
    AND t.tabname = i.tabname
    ORDER by card desc,
    indname;
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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