_tablestat-read statistics on wrong table when there is a lob

_tablestat-read shows results for unrelated tables when the table being queried contains a lob
PROMON (Activity Summary) confirms the total number of records read

Simple "FOR EACH" query does not show unrelated table's _tablestat-read
If  BUFFER-COPY uses no-lobs to populate the temp-table, unrelated table's do not show _tablestat metrics
Temp-Table or ProDataset FILL() shows unrelated table's _tablestat-read metrics

There are no triggers that reference the unrelated table's metrics
The ProDataset, DATA-SOURCE buffers does not reference the unrelated tables

The table contains LOB objects.
Database is started with appropriate values for -tablerangesize and -tttablerangesize


 
 

1. Add two BLOB fields to the SalesRep table (sports2000 database)

• SmallImage
• LargeImage

2. Run: proutil dbname -C zerostats

3. Run the following ABL code: 

DEF VAR mPtr AS MEMPTR.

FOR EACH salesrep:
    mPtr = salesrep.SmallImage.
    SET-SIZE(mPtr) = 0.
    mPtr = salesrep.LargeImage.
    SET-SIZE(mPtr) = 0.
END.

4. Query the _tablestat metrics.  Apart from SalesRep showing Reads, the unrelated Employee and Department tables also show the same number of reads.

FOR EACH _tablestat NO-LOCK  WHERE _tablestat-read  > 0:
   FIND _file no-lock WHERE _file-num = _tablestat-id.
   DISPLAY
     _file-name
     _tablestat-id
     _tablestat-read
   WITH WIDTH 100.
END.

 

Access to the LOB data causes _table-stats (and PROMON) report reads that do not occur. In part this is due to the fact that there is no backward pointer from LOB records where the object number of the lob is being used to their parent table record.

During the record read, the object Id of the lob is incorrectly being used to collect table stats.
There is no place to add rm statistics for the lobs.

Associating rm stats for associated lobs in with the table stats is confusing since lob and record stats are different:

• When reading a lob there can be more record read stats than expected as (records read + lobs read) is reported as records read.
• When a lob field is updated in a record, it may generate multiple lob updates in the stats. From this perspective, lob updates are more like a record updates that involves many record fragments.
• When deleting a lob this will be reported as deleting a record of the associated table which is incorrect
• When deleting one record, one or more lobs will be deleted resulting the the tablestat reporting multiple records deleted

Upgrade to OpenEdge 11.7.1 Service Pack where LOB activity is no longer recorded as table activity in the _tablestat statistics.

In OpenEdge 12.0, Large Object (LOB) Activity Statistics were introduced.

Database Startup Parameters:

• -baselob : Can be queried and is modifiable at runtime with VST _Dbparams
• -baseuserlob : Can be queried and is modifiable at runtime with VST _Dbparams
• -lobrangesize : Can be queried but is not modifiable at runtime with VST _Dbparams

VST tables:

The following VST tables record LOB activity:

• _LobStat 
• _UserLobStat