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

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.