Monitoring user I/O

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 and _UserIndexStat VSTs collect the data. For details on the table fields, see VirtualSystem Tables. You can query the VSTs directly or use PROMON to monitor the activity. For table activity, choose PROMON > R&D > Other Displays > I/O Operations by User by Table; for index activity, choose PROMON > R&D > Other Displays > I/O Operations by User by Index.

There is a cost in memory consumption to monitor I/O by user. The estimated cost is 32 bytes per table per user, and 40 bytes per index 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 and indexes monitored by the _UserTableStat and _UserIndexStat VSTs is 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 44. 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.¹ If not specified, the range is 100.

¹ You cannot alter the range after startup.