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.