R
Rob Fitzpatrick
Guest
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.
Continue reading...
Continue reading...