Table and index activity can be monitored on a per-user basis. This granularity can provide insight into individual user activity, and provide data for evaluating query efficiency. The _UserTableStat, _UserIndexStat, and _UserLobStat VSTs collect the data. For details on the table fields, see Virtual System Tables. You can query the VSTs directly or use PROMON to monitor the following:
  • Table activity—Choose PROMON > R&D > Other Displays > I/O Operations by User by Table
  • Index activity—Choose PROMON > R&D > Other Displays > I/O Operations by User by Index
  • Large object activity—PROMON > R&D > Other Displays > I/O Operations by User by LOB

There is a cost in memory consumption to monitor I/O by user. The estimated cost is 40 bytes per table per user, 48 bytes per index per user, and 40 bytes per LOB per user. Consider a 500 user system with 100 tables and 200 indexes. The total memory cost is as follows:

500 users * 100 tables * 32 bytes/table = 
                              1,600,000 bytes of memory for tables

500 users * 200 indexes * 40/bytes/index = 
                              4,000,000 bytes of memory for indexes

1,600,000 + 4,000,000 = 5,600,000 bytes, or approx 5.3 MB

The base and range of tables, indexes, and LOBs monitored by the _UserTableStat, _UserIndexStat, and _UserLobStat VSTs are established at server startup. By default, the first 100 objects are monitored. The table below describes the startup parameters. You can alter the base during runtime, but not the range.

Table 1. Startup parameters for I/O by object
Parameter Indication
-basetable n Start monitoring tables at table n. The default is 1. Change the basetable value during runtime by updating the value of the _TableBase field in the _StatBase VST.
-tablerange n Monitor n tables. If not specified, the range is 100.
-baseindex n Start monitoring indexes at index n. The default is 1. Change the baseindex value during runtime by updating the value of the _IndexBase field in the _StatBase VST.
-indexrange n Monitor n indexes.1 If not specified, the range is 100.
-baselob n Start monitoring indexes at LOB n. The default is 1. Change the baselob value during runtime by updating the value of the _LobBase field in the _StatBase VST.
-lobrangesizen Monitor n large objects. If not specified, the range is 50.

To consume less memory, set the -indexrange, -tablerange and -lobrangesize parameters to smaller values.

1 You cannot alter the range after startup.