onstat -P Partition Buffer Summary

The onstat -P command displays a list showing the partition number and the number of btree, data and other types of pages in the buffer pool associated with each partition. It also identifies the number of resident and dirty pages in the buffer pool for each parititon. The output is sorted by partition number.

Buffer pool page size: 2048
partnum  total    btree    data     other    dirty
0        16595    2        6        16587    4
1048577  4        0        2        2        0
1048578  2        1        1        0        0
1048580  27       11       16       0        0
1048581  4        3        1        0        0
.....
Totals:  2999900  9993     2969895  20012    2160

Percentages:
Data  99.00
Btree 0.33
Other 0.67

partnum  total    btree    data     other    dirty
0        133      23       100      10       0
128974849 14       0        13       1        0
128974850 1        0        0        1        0
128974855 1        1        0        0        0
128974856 88       87       0        1        0
128974857 4        3        0        1        0
128974858 8        7        0        1        0
128974859 3130     2752     0        378      0
.....
Totals:  499985   426051   524      73410    17866

Percentages:
Data  0.10
Btree 85.21
Other 14.68
</div>

Output Descriptions

Heading	Description	Format	See Also
partnum	The part number for the table/index	Dec	onstat -g ppf
total	The total number of pages in the buffer pool for this partition.	Dec
btree	The total number of index pages in the buffer pool for this partition.	Dec
data	The total number of data pages in the buffer pool for this partition.	Dec
other	The total number of other pages in the buffer pool for this partition (e.g., bitmap).	Dec
resident	The total number of partition pages that are marked as resident.	Dec
dirty	The total number of partition pages that are marked as dirty.	Dec	onstat -R

Percentages

Label	Description	Format	See Also
data	The percentage of pages in the buffer pool that are data pages.	Flt
btree	The percentage of pages in the buffer pool that are index pages.	Flt	onstat -C
other	The percentage of pages in the buffer pool that are neither data pages nor index pages.	Flt

Notes

Partitions that contain both index and data pages will have a value in both the Btree and Data columns. Fragmented (detached) indexes will have a separate partition entry.

The first column of the total line should equal the number of pages in the buffer pool.

Data pages consist of data, remainder and blob pages.

Since V10 and the introduction of user defined page size the individual buffer pools can be seen via onstat -P <page size>

Monitoring and Tuning

The output from onstat -P is useful for identifying how much of a partition (table) is cached and whether or not its pages have been marked as resident. Heavily hit tables, such as reference tables, are good candidates for use of the resident capability offered by the CREATE TABLE statement.