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