sys_ra_ptn_stats

The sys_ra_ptn_stats table contains read-ahead statistics broken down by partition. The same information is shown by the onstat -g rah partitions command.

Table 1. sys_ra_ptn_stats table information

Column Type Description
partnum integer Partition number of table or index being read
bfcread integer Number of buffer reads
dskreads integer Number of disk reads
hit_ratio smallint Percentage of total reads that were cached
data_reqs integer Number of RA requests for data
data_npages integer Number of data pages read ahead
data_nios integer Number of I/Os performed for the data read-aheads
data_eff smallint Effectiveness of data read-aheads -- higher with more I/O
leaf_reqs integer Number of RA requests for leaves
leaf_npages integer Number of leaf pages read ahead
leaf_nios integer Number of I/Os performed for the leaf read-aheads
idx_eff smallint Effectiveness of leaf read-aheads -- higher with more I/O
idxdata_reqs integer Number of index/data RA requests
idxdata_npages integer Number of data pages read ahead for I/D requests
idxdata_nios integer Number of I/Os performed for the I/D read-aheads
idxdata_eff smallint Effectiveness of I/D read-aheads -- higher with more I/O
logrec_npages integer Number of pages read ahead during rollback
logrec_nios integer Number of I/Os required to process those pages
logrec_eff smallint Effectiveness of these RA requests
lc_nreqs integer Number of last-committed RA requests
lc_nused integer Number of those pages used by last-committed code
lc_resched integer Number of request reschedules due to contention
lc_fail integer Number of times a last-committed read failed
lc_eff smallint Effectiveness of the LC read-ahead requests
ptn_reqs integer Number of RA requests for partition pages
ptn_npages integer Number of pages read ahead by these requests
ptn_nios integer Number of I/Os required to process these requests
ptn_eff smallint Effectiveness of ptn read-aheads
rapgs_used integer Number of pages that were read ahead and actually used by scans

The "effectiveness" of read-ahead requests is somewhat counter-intuitive. The more I/O required, the more effective they are judged to be. The reasoning is as follows: a read-ahead request is made with the assumption that the target page is not already in the buffer pool. If the page is already cached, the overhead required to request a read-ahead for that page is wasted effort. Every read-ahead operation that must go to disk for the page has in theory increased the efficiency of the requestor by seeding the buffer pool with pages it will need in the future. By performing I/O it has been effective. A read-ahead request that results in no read, because the page was discovered in the buffer pool, has expended energy without helping the requestor and is therefore considered ineffective.