onstat -g ppf Partition profile

The onstat -g ppf command displays the profile for all active partitions (tables/indexes). The output is sorted by partition number.

Partition profiles
partnum    lkrqs lkwts dlks  touts isrd  iswrt isrwt isdel bfrd  bfwrt seqsc rhitratio
0x2        0     0     0     0     12220784 0     0     0     11762767 0     0     49
0xa        0     0     0     0     0     0     0     0     0     0     0     0
0xc        0     0     0     0     19286069 0     0     0     4423714 393238 0     100
0xf        0     0     0     0     2998  0     0     0     9897504 192136 0     82
0x17       0     0     0     0     266036699 0     0     0     523263 570005 0     100
0x18       0     0     0     0     197944677 0     0     0     0     0     0     0
0x19       0     0     0     0     200334048 199053731 0     0     0     0     0     0
0x1a       0     0     0     0     604872 0     0     0     0     0     0     0
0x1b       0     0     0     0     155098 0     0     0     0     0     0     0
0x1c       0     0     0     0     1933024 0     0     0     0     0     0     0
0x24       0     0     0     0     5950  0     0     0     0     0     0     0
0x26       0     0     0     0     5946  0     0     0     0     0     0     0
0x28       0     0     0     0     8990  0     0     0     0     0     0     0
0x29       0     0     0     0     1199283 0     0     0     0     0     0     0
0x46       0     0     0     0     6     0     0     0     0     0     0     0
0x47       0     0     0     0     9     0     0     0     0     0     0     0
0x402      0     0     0     0     0     409   0     0     0     0     0     0
0x100001   0     0     0     0     0     0     0     0     25725 0     0     97
0x100002   177079814 0     0     0     49979594 0     0     0     151982312 1     24    100
0x100003   0     0     0     0     0     0     0     0     0     0     0     0
0x100004   117142641 0     0     0     53908229 0     0     0     126466642 0     0     100
0x100005   141749 0     0     0     69365 0     0     0     150772 0     0     98
0x100006   16438 0     0     0     6740  0     0     0     22686 0     0     65

Output Description

Heading Description Format See Also
partnum The partnum in decimal format of the partition. Dec onstat -P
lkrqs The number of lock requests. Dec  
lkwts The number of lock waits. Dec  
dlks The number of deadlocks. Dec  
touts The number of deadlock timeouts. Dec  
isrd The number of reads against this partition. Dec  
iswrt The number of writes against this partition. Dec  
isrwt The number of rewrites (updates) against this partition. Dec  
isdel The number of deletes against this partition. Dec  
bfrd The number of buffer reads for this partition. Dec  
bfwrt The number of buffer writes for this partition. Dec  
seqsc The number of sequential scans against this partition. Dec  

Notes

    If the TBLSPACE_STATS configuration parameter is set to 0, then the onstat -g ppf command displays: Partition profiles disabled. The minor overhead is worth the extra processing for the information that can gleaned from the output.

Monitoring and Tuning

    The onstat -g ppf output is invaluable for identifying heavily hit partitions.

    To capture the partition number and the number of sequential scans when they exceed, for example 69, execute the following command:
onstat -g ppf | awk '{ if ($NR > 69) print $1, $NR}' - | sort -n -r -k +2.0