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!!
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.
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!! =)