Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184

    Unanswered: Identify Index being used

    Using Snapshot Monitoring data is it possible to know which indexes are being used by an application.

    So also, can we identify the indexes not being used by an application.

    Of couse, we can use visual explain on SQL statements to identify the index being used. This is not practically possible for an application with Dynamic SQL running into 1000s.

    In Oracle 9i, we have a new feature called 'monitoring usage' for an index, which can be switched on / off.

    TIA.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    An index which has not been used up to now, can possibly be used intensively from tomorrow on, e.g. when the table size changes drastically, or actually when RUNSTATS is performed after such a change.

    So I would say that one should rather look at the typical database use, when deciding to add or drop indexes: e.g., with a read-only database, add as many indexes as possible.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Peter.Vanroose
    So I would say that one should rather look at the typical database use, when deciding to add or drop indexes: e.g., with a read-only database, add as many indexes as possible.
    That sounds good in theory, but there rarely is a true read only database, since usually the data must be loaded. For large data loads, too many indexes can cause many problems.

    Also, even in a true read only database, DB2 might spend more time evaluating the best access path for each query if there were more indexes than are ncecessary. So I would always suggest that one understand when an index will never be used by DB2 (primarily because of low cardinality) and never create those kind of indexes.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Marcus_A
    So I would always suggest that one understand when an index will never be used by DB2 ...
    Agreed.

    Usefulness of an index is normally easily deduced from the meaning of (& expected contents of & expected querying on) a certain column or set of columns.

    Gathering statistics (with e.g. the "monitoring usage" tool of Oracle) could be a means of finding out about usefulness of indices, but most of the time the meta-level "understanding" of the tables is far more reliable in this respect.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Nov 2005
    Posts
    1

    Talking

    do not forget the horrible probability of an unused index actually helping you get a good access path. What?? I hear you say...well we just got it here with DB2 z/OS 7.1. We created a "bad" index with terrible cluster ratio BUT with two columns that were the 2nd and 4th columns in another index. Using EXPLAIN with the bad index there (After RUNSTATS of course!) the first index was used and all was OK. After dropping the bad index an EXPLAIN showed that the table join order was different and the performance of the query was terrible! Reason: The "bad index" contained "extra" info that the Optimizer could use to see that the filter factor of the combined fields was good (This data would only be available in V7 when we use DSTATS). So the new answer for this old question is probably:-

    There is *never* an unused DB2 index

    Roy

Posting Permissions

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