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

Posted by Progress USER on 07-Jan-2019 19:22

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!!

Posted by Rob Fitzpatrick on 07-Jan-2019 20:18

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

Posted by Rob Fitzpatrick on 07-Jan-2019 20:18

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.

Posted by Progress USER on 07-Jan-2019 21:30

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

This thread is closed