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
[View:/cfs-file/__key/communityserver-discussions-components-files/18/TableIndexStat.txt:320:240]
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.
George, You may be interested to hear that there are new startup parameters in OE 11.7 used to customize the tracking of usage statistics by user (_usertablestat).
See documentation.progress.com/.../index.html
I'm assuming the purpose of these new parameters (eg usertablestatrangesize) is to *disable* statistics at the user level since they perform so badly in practice. For example these "per-user" usage stats make the "OE Explorer" almost unusable when clicking on individual database users because those web clicks cause adminserver to attemp to pull in all table and index stats for the user. (more on that here: community.progress.com/.../30910 )
Unless you are already using CDC, these changes in the usage statistics parameters might be one of the more exciting RDBS changes since OE 11.6.
The poor memory allocation by OEE was addressed in an 11.7 service pack. It shouldn't blow up anymore when usertabelstat or userindexstat or the other two are set to high values.
> You may be interested to hear that there are new startup parameters in OE 11.7 used to customize the tracking of usage statistics by user (_usertablestat).
I know them but I think that their purpose is only to limit the memory usage for _UserTableStat (and for _UserIndexStat). The new parameters do not affect the db low level events that are used to increment the correspondent countres. Again it's not a bug that the _TableStat statistics is not exactly equal to the the number of records that were created/deleted/updated. But on on other hand we should not adhere to the illusions that the _UserTableStat statisics is 100% accuarete.
> The poor memory allocation by OEE was addressed in an 11.7 service pack.
I guess you're talking about the following db startup parameters:
Starting table number per user for statistics range (-baseusertable): <num> (18404)
Number of tables per user included in statistics collection (-usertablerangesize): <num> (18405)
Starting index number per user for statistics range (-baseuserindex): <num> (18406)
Number of indexes per user included in statistics collection (-userindexrangesize): <num> (18407)
- Was that Service Pack 1 for 11.7 ?
The poor memory allocation by OEE was addressed in an 11.7 service pack. It shouldn't blow up anymore when usertabelstat or userindexstat or the other two are set to high values.
The poor memory allocation by OEE was addressed in an 11.7 service pack. It shouldn't blow up anymore when usertabelstat or userindexstat or the other two are set to high values.
... and if that doesn't work, we can now use the new OE 11.7 parameters to disable "per-user" usage stats without losing them altogether for the system as a whole. As soon as I saw those "per-user" usage stat parameters, it felt like a load was lifted. (I'm not sure why the integer counters - tracked by table and active user - were so challenging to maintain and resource-intensive to query.)
It would be nice to be able to find these OEE issues in the KB. I get the feeling that this OEE/OEM stuff is not widely used, or at least not on HP-UX. Maybe it was built for Windows/Linux the software QA was only done on that side of things. The HP-UX guys seem to like their CHUI tools - like promon or protop or whatever - and maybe they don't even attempt to use new-fangled graphical management tools like OEE/OEM. ;-)
The usage stats are the primary reason that OEE U/I becomes sluggish. Its worse than a simple usability issue because the gathering of the stats will actually swamp admin server (java) on the back-end at 200% for over ten minutes, causing a bunch of pain for the system as a whole.
Here is a suggestion... Why not put the usage statistics in a subsequent link that you would click if you wanted it? Then the information could be pulled in on-demand, and not whenever we need to see basic details about a database connection (ie. login time, i.p. address, active transactions, and so on).
Hi Matt. Thanks again for the tip about our problems with usage stats. I think I found the OEE fix you were referring to. (in the 11.7.1 readme). It refers to usage stats and memory troubles in OEE/OEM.
PSC00357066 : Accessing OpenEdge Management User details page may trigger an
Out of memory exception.
================================================================================
Accessing the User details page for a managed or scripted database in OpenEdge
Management may trigger an Out of memory exception. This can cause the
AdminServer to crash, if
ò The database has large number of tables or indexes.
ò The table or index range size for the database is increased to a
significantly large number.
ò The database is configured on a local or remote AdminServer prior to OpenEdge
11.7.1.
As a workaround, you can reduce the index or table size startup parameters.
I suspect that another workaround in version 11.7 is probably to leave regular table and index stats alone, but adjust the per-user stats so they are disabled.
Any chance that I can get this fix included in the 11.6.4 service pack? We are only now upgrading from 11.3 to 11.6 and I don't expect we'll get to another version for many years.