DBAnalys to the Rescue. Untold stories.

Dmitri Levin and me did the presentation in Copenhagen: DBAnalys to the Rescue. Version 2.0
ftp://ftp.progress-tech.ru/pub/Users/george/DBAnalys2_Copenhagen.pptx
Unfortunately we did not cover all planned topics. Really it was my fault - I had to speak three times faster. ;-)
I hope no one will mind if I'll post the untold topics on the Community.

Synergy.

Everybody knows that _IndexStat VST has the _IndexStat-create and _IndexStat-delete fields but it does not have the updates. When the indexed fields are updated the correspondent keys are deleted from one index block and new ones are created and probably in the different index block. The existent fields in the _IndexStat table are describing this "physical" process. But from a logical point of view the _IndexStat has only "updates".
Let's take the difference between _IndexStat-create and _TableStat-create. What does it give us? Index updates, of course.
Let's take the difference between _IndexStat-delete and _TableStat-delete. Again index updates?
If the results are the same in both cases then why to report four fields? It would be enough to report the table's creates/deletes and the index's updates.

If the results are the same... But are they really the same? Yes... and no. As always the answer is ambiguous. If 100% transactions are committed then the both values above exactly equal. On other hand during transaction undo Progress updates the counters in _IndexStat but not in _TableStat. In this case (_IndexStat-create - _TableStat-create) will not be equal (_IndexStat-delete - _TableStat-delete). We has called the difference between these two values as the "undo noise". In real life the undoes used to 0.1% compared to the commits. Hence the "undo noise" is much less than the index updates (any of two values above).

The "undo noise" is useful by itself - you can see which tables were involved in the transactions that were undone. If the value is high then an application probably checks some conditions not at the transaction's beginning and if the checks are failed then the changes created by transaction will be undone.

Back to the index updates... Obviously its value can be smaller than _TableStat-update. For example, because the non-indexed fields can be updated. But also the index updates can be much higher than _TableStat-update. For example, the statistics from the real application shown that one index was updates a million times per one record's update. More over it turned out that the ratio of index updates to table updates was exactly equal to the number of records in another table that has a field related to the indexed field in first table. _TableStat-update does not return the number of times a record was updated in your code. Progress postpones the writes of the record's changes to a databases as long as possible. Your code may update a record many times but the changes will be saved by one db write and _TableStat-update will increment only by one. But abnormally high ratio as in the described case was a result of bad coding. We did not see this anomaly when we dealt with the _IndexStat-create and _IndexStat-delete fields.

The indexes can be multi-component. So we can ask what field causes the index updates. And sometimes we can get the answer. For example, when the same field is a component of a few indexes:
Index1 = field1 + field2
Index2 = field2 + field3
If the updates of Index2 are zero then the 100% updates of Index were caused by updates of field1.
If the updates of Index2 are, let's say, a half of the updates of Index1 then we would write:
Updates of Index1 = 100%field1,50%field2
It can be translated as: only 50% of index updates can be caused by field2 or 100% of index updates can be caused by field1.
In real life the same field can be a part of many indexes. So we will get more or less accurate estimation of the contribution from each field to the index updates.

You can see how it can be coded in the Dmitri Levin's program:
proora.com/.../DbStat.html

Best regards,
George