You can obtain both statistics on buffer use and information on specific buffers.
The statistical information includes the percentage of data writes that are cached to buffers and the number of times that threads had to wait to obtain a buffer. The percentage of writes cached is an important measure of performance. (For information on how to use this statistic to tune the database server, see your IBM Informix: Performance Guide.) The number of waits for buffers gives a measure of system concurrency.
Information on specific buffers includes a listing of all the buffers in shared memory that are held by a thread. This information allows you to track the status of a particular buffer. For example, you can determine if another thread is waiting for the buffer.
You can use the following command-line utilities to monitor buffers:
Execute onstat -p to obtain statistics about cached reads and writes. The following caching statistics appear in four fields on the top row of the output display:
Figure 45 shows these fields.
Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 382 400 14438 97.35 381 568 3509 89.14 ...
The number of reads or writes can appear as a negative number if the number of occurrences exceeds 232.
The onstat -p option also displays a statistic (bufwaits) that indicates the number of times that sessions had to wait for a buffer.
Execute onstat -B to obtain the following buffer information:
Figure 46 shows an example of onstat -B output.
Buffers address userthread flgs pagenum memaddr nslots pgflgs xflgs owner waitlist 849ae8 0 86 100955 84e000 1 b0 0 0 0 849b40 0 6 10095b 84e800 0 4 0 0 0 849b98 0 6 1009eb 84f000 0 4 0 0 0 849bf0 0 6 1008f5 84f800 2 70 0 0 0 ... 84dea0 0 86 10093e 8b0800 8 1 0 0 0 84def8 0 6 10094b 8b1000 0 4 0 0 0 84df50 0 86 1009cd 8b1800 9 b0 0 0 0 0 modified, 200 total, 256 hash buckets, 2048 buffer size
Execute onstat -b to obtain the following information about each buffer:
You can compare the addresses of the user threads to the addresses that appear in the onstat -u display to obtain the session ID number. Figure 47 shows sample output. For more information on the fields that onstat displays, see the utilities chapter of the IBM Informix: Administrator's Reference.
Buffers address userthread flgs pagenum memaddr nslots pgflgs xflgs owner waitlist 84a748 0 27 1012b0 860000 19 2001 80 8067c4 0 84add0 0 0 101752 869800 19 2001 80 807890 0 84b2a0 0 27 100c31 870800 19 2001 80 8067c4 0 84c798 0 27 10108e 88f000 19 2001 80 8067c4 0 84d818 0 27 101272 8a7000 19 2001 80 8067c4 0 154 modified, 200 total, 256 hash buckets, 2048 buffer size
Execute onstat -X to obtain the same information as for onstat -b, along with the complete list of all threads that are waiting for buffers, not just the first waiting thread.
Query the sysprofile table to obtain statistics on cached reads and writes and total buffer waits. The following rows are relevant.