DBAnalys to the Rescue. Untold stories.

Posted by George Potemkin on 18-Nov-2015 10:34

Dmitri Levin and me did the presentation in Copenhagen: DBAnalys to the Rescue. Version 2.0
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.


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:

Best regards,

All Replies

Posted by Tai Li on 18-Nov-2015 20:03

Hi Gorge, the download link to 'DBAnalys2_Copenhagen.pptx' is incomplete. Thanks.

Posted by George Potemkin on 19-Nov-2015 00:18

I has fixed the link

Posted by Andriy Mishin on 19-Nov-2015 02:22

Good research. Thank you!

Posted by George Potemkin on 19-Nov-2015 08:22

Thanks Andriy!

A bit off-topic or the topic: "what you get from VSTs is not what you should report".

_IndexStat-split/_IndexStat-blockdelete supposed to return the number of blocks added to and deleted from an index tree. This information is useful if you are monitoring the fast growing indexes or the indexes that need a close "attention" of idxcompact utility. Unfortunately there is a bug that was fixed only in 11.6:
PSC00334196: Incorrect values reported by _IndexStat-split/_IndexStat-blockdelete
Before Progress 11.6 the IndexStat-blockdelete is always 0 and _IndexStat-split returns the sum of splits and deletes.

But you can use a workaround (or estimation):
if _IndexStat-blockdelete eq 0 then
assign BlockDelete = _IndexStat-split * _IndexStat-delete / (_IndexStat-delete + _IndexStat-create).
BlockSplit = _IndexStat-split - _IndexStat-blockdelete.

They return the sum of the allocated as well as active transactions that were committed or undone. The allocated transactions create only minor activity in database and that is why they can be ignored. When you report bi activity per transaction or the table's creates/deletes/updates per transaction you should take into account only active transactions. Percent of the allocated transactions highly depends from an application and may vary from 20% to 80% of all transactions. Each allocated transaction creates 3 TXT latch locks. Each active transaction creates 4 TXT latch locks. Sometimes (but under very specific conditions) it can create 5 TXT latch locks. If we will ignore the last case then the number of allocated transactions can be estimated as:
max(0, 4 * _Summary-Commits - _Latch-Lock)
for _Latch were _Latch-Id eq 9 or _Latch-Name eq "MTL_TXT".

The number of active transactions is _Summary-Commits minus the number of allocated transactions.
It should be equal, for example, to the number of transactions reported by rfutil -C aimage scan.

Also you can report the number of the unused TRIDs. As you know Progress allocated the bunch of 128 TRIDs at once that can be used (or not being used) by any session.
The number of unused TRIDs is an increase of _MstrBlk-lasttask minus _Summary-Commits.


Posted by Dmitri Levin on 19-Nov-2015 10:10

Some of the Untold Stories, i.e. Synergy part, we plan to present at PUG Challenge Americas 2016.

Posted by Peter Judge on 19-Nov-2015 10:13

> Some of the Untold Stories, i.e. Synergy part, we plan to present at PUG Challenge Americas 2016.

You can do that only once. Then they are Told Stories :D


Posted by George Potemkin on 19-Nov-2015 11:38

Some "stories" were not ready for EMEA PUG Challenge in Copenhagen. Dbanalys is a bottomless topic! ;-)

Posted by gus on 20-Nov-2015 10:38

> On Nov 19, 2015, at 9:23 AM, George Potemkin wrote:


> As you know Progress allocated the bunch of 128 TRIDs at once that can be used (or not being used) by any session.

depends on the transaction table size


gus (gus@progress.com)

"Debugging is twice as hard as writing the code in the first place.

Therefore, if you write the code as cleverly as possible, you are,

by definition, not smart enough to debug it." -- Brian Kernighan

This thread is closed