G
George Potemkin
Guest
The _TableStat-update/create/delete fields are close to but NOT exactly equal to the number of records that were updates/created/deleted. In other words, those counters can be incremented without the correspondent actions with the records. Also it’s possible to increment the _IndexStat-create/delete counters without the changes of any field in_TableStat and vice versa. Documentation says that these fields store the number of times update/create/delete “access” has occurred to the table. So I decided to test what does an “access” mean, what low level database events increment those counters and when. Example of the tests: DO TRANSACTION: CREATE tbl. ASSIGN tbl.IndexedField = 1. UNDO. END. Triggers fired on: CREATE _TableStat / _IndexStat: Table - Read: 0 Create: 1 Delete: 0 Update: 0 Index - Read: 0 Create: 1 Delete: 1 Table Create and Index Create are changing after ASSIGN statement. Index Delete is changing after UNDO. Recovery notes (RL_): Non-unique: TBGN RMDEL RMCR CXINS CXREM RMDEL RMCR TEND Unique idx: TBGN RMDEL RMCR CXREM CXINS IXDEL BKREPL RMDEL RMCR TEND (Please visit the site to view this file) After the execution of the above code we will not create a new record but _TableStat-create was incremented by one while _TableStat-delete did not changed. So according to the _TableStat statistics it looks like a new record was created. It’s not a bug but a feature. The attached file contains the results of the similar tests. My conclusions from the tests: The _TableStat-create/delete counters are incremented by each CREATE/DELETE statement no matter if the statements will be successful or not (for example, due to the UNDO). But these counters will be incremented only if (and only when) a transaction status is changed to “Active”, in other word, not each CREATE/DELETE statement executed by 4GL code will increment these counters. The _TableStat-update counter is incremented each time when Progress generates RMCHG recovery notes. Progress postpones this event as long as possible. For example, RMCHG note is often generated on the END of transaction block. Number of the ASSIGN statements does not always matter. Also the fired WRITE trigger does not mean that RMCHG note will be generated. In other words, it's impossible to define a "record update" in a few words. Also _TableStat-update counter can be incremented without real changes in the records. Note that a record create (_TableStat-create) does not imply an update, in other words, a record created will not always increment _TableStat-update. The _IndexStat-create/delete counters are incremented each time when Progress generates CXINS and CXREM (BKREPL) recovery notes (BKREPL note is used instead of CXREM for the unique indexes - BKREPL note just means a replacement of old index key by an "index entry lock"). These counters can be incremented without the changes in the _TableStat statistics. Transaction undo generates the “reverse” recovery notes. So if _IndexStat-create (or_IndexStat-delete) counter was incremented during the active phase of transaction then _IndexStat-delete (or _IndexStat-create) counter will be incremented by the same value during transaction undo. The above, of course, applies to the _UserTableStat/_UserIndexStat as well as to the correspondent statistics per database. The _TableStat-read and _IndexStat-read counters were not a part of this mini research. If I remember correctly they are incremented when Progress creates the BKSH lock (shared lock on block in buffer pool). But in fact the things are much more complicated with the read statistics. For example, some Progress processes are contributing to the read statistics per database but not to their own _UserTableStat-read. But maybe my memory does not serve me correctly there. In conclusion: there is a difference between these counters and real db events the counters represent. In the rare cases it might be important to remember.
Continue reading...
Continue reading...