HOW CAN I MONITOR THE USE OF THE INDEXES OF MY DATABASE? - Forum - OpenEdge General - Progress Community

HOW CAN I MONITOR THE USE OF THE INDEXES OF MY DATABASE?

 Forum

HOW CAN I MONITOR THE USE OF THE INDEXES OF MY DATABASE?

This question is answered

Hi,  I need  to know  how much  the index of my database are used,  i  think i have some index  that   are not used  and they are only using space and slowing performance on inserts and updates of my tables.

For example i would like to know the information of  number of scans, number of updates,  deletes, inserts,  reads  of  the index,   since the database  was started up.

¿is there a way to monitor  this values of the index usage?

i have   OpenEdge  10.1C  runing on AIX 5.3,  thank you!!

Verified Answer
  • Assuming your VST schema is up to date (via proutil dbname -C updatevst; done offline), you can query the _IndexStat virtual system table (VST) to get information about index reads, creates, deletes, and block splits.  There is no "updates" field as index keys are not updated.  A record update involving a key field in an index causes a delete of the existing key and a create of a new one with the updated value.  The schema of the _IndexStat table is in the Reference section of the DB Admin manual.

    This table won't tell you about "scans".  If you mean WHOLE-INDEX scans caused by a query predicate that has no matching index to permit bracketing, you can get information on these, at least from most static queries, by parsing COMPILE XREF output.

    There is also a per-user version of this VST, called _UserIndexStat.  I think it was added in 10.1C.  You can cross-reference it with _Connect to get information about index accesses by your current users.  By contrast, _IndexStat gives you overall statistics since the database started.

    Try this:

    for each dictdb._indexstat no-lock,
      each dictdb._index no-lock 
      where _index._idx-num = _indexstat._indexstat-id 
      by _indexstat._indexstat-read descending:
    
      find dictdb._file no-lock where recid(_file) = _index._file-recid no-error.
      
      display
        _indexstat._indexstat-id
           column-label "Index"
           format "->>>>9"
        _file._file-name  
           column-label "Table name"
           format "x(32)"
        _index._index-name          
           column-label "Index name"
           format "x(32)"
        _indexstat._indexstat-read
           column-label "Reads"
           format ">,>>>,>>>,>>>"
    /*
        _indexstat._indexstat-osread
           column-label "O/S Reads"
           format ">,>>>,>>>,>>>"
    */
        _indexstat._indexstat-create
           column-label "Creates"
           format ">>,>>>,>>>"
        _indexstat._indexstat-delete
           column-label "Deletes"
           format ">>,>>>,>>>"
        _indexstat._indexstat-split
           column-label "Bl. splits"
           format ">>,>>>,>>>"
      .
    end.

    I commented out the_indexstat-osread field as I'm not sure if that's in your version of the schema.

    The number of records in _IndexStat is determined by the values of two primary broker startup parameters: -baseindex and -indexrangesize.  They default to 1 and 50 respectively.  The first record will be for the index whose idx-num is ( -baseindex ).  The last record will be for the index whose idx-num is ( -baseindex + -indexrangesize - 1 ).  You can generally leave -baseindex at the default, but you will want to change -indexrangesize accordingly for the size of your schema.  For example, if your highest-numbered application index has idx-num 900, then set -indexrangesize to at least 900.  I generally set it higher to accommodate online schema changes.

    All of the above concepts apply to table statistics as well.  There are VSTs called _TableStat and _UserTableStat, and startup parameters called -basetable and -tablerangesize.

All Replies
  • Assuming your VST schema is up to date (via proutil dbname -C updatevst; done offline), you can query the _IndexStat virtual system table (VST) to get information about index reads, creates, deletes, and block splits.  There is no "updates" field as index keys are not updated.  A record update involving a key field in an index causes a delete of the existing key and a create of a new one with the updated value.  The schema of the _IndexStat table is in the Reference section of the DB Admin manual.

    This table won't tell you about "scans".  If you mean WHOLE-INDEX scans caused by a query predicate that has no matching index to permit bracketing, you can get information on these, at least from most static queries, by parsing COMPILE XREF output.

    There is also a per-user version of this VST, called _UserIndexStat.  I think it was added in 10.1C.  You can cross-reference it with _Connect to get information about index accesses by your current users.  By contrast, _IndexStat gives you overall statistics since the database started.

    Try this:

    for each dictdb._indexstat no-lock,
      each dictdb._index no-lock 
      where _index._idx-num = _indexstat._indexstat-id 
      by _indexstat._indexstat-read descending:
    
      find dictdb._file no-lock where recid(_file) = _index._file-recid no-error.
      
      display
        _indexstat._indexstat-id
           column-label "Index"
           format "->>>>9"
        _file._file-name  
           column-label "Table name"
           format "x(32)"
        _index._index-name          
           column-label "Index name"
           format "x(32)"
        _indexstat._indexstat-read
           column-label "Reads"
           format ">,>>>,>>>,>>>"
    /*
        _indexstat._indexstat-osread
           column-label "O/S Reads"
           format ">,>>>,>>>,>>>"
    */
        _indexstat._indexstat-create
           column-label "Creates"
           format ">>,>>>,>>>"
        _indexstat._indexstat-delete
           column-label "Deletes"
           format ">>,>>>,>>>"
        _indexstat._indexstat-split
           column-label "Bl. splits"
           format ">>,>>>,>>>"
      .
    end.

    I commented out the_indexstat-osread field as I'm not sure if that's in your version of the schema.

    The number of records in _IndexStat is determined by the values of two primary broker startup parameters: -baseindex and -indexrangesize.  They default to 1 and 50 respectively.  The first record will be for the index whose idx-num is ( -baseindex ).  The last record will be for the index whose idx-num is ( -baseindex + -indexrangesize - 1 ).  You can generally leave -baseindex at the default, but you will want to change -indexrangesize accordingly for the size of your schema.  For example, if your highest-numbered application index has idx-num 900, then set -indexrangesize to at least 900.  I generally set it higher to accommodate online schema changes.

    All of the above concepts apply to table statistics as well.  There are VSTs called _TableStat and _UserTableStat, and startup parameters called -basetable and -tablerangesize.

  • Thank you very much  Rob,  it really  works!!   =)