Tables that use data-only locking do not have page chains like allpages-locked tables. To perform a table scan on a data-only-locked table, Adaptive Server:
Reads the OAM (object allocation map) page(s) for the table
Uses the pointers on the OAM page to access the allocation pages
Uses the pointers on the allocation pages to locate the extents used by the table
Performs either large I/O or 2K I/O on the pages in the extent
The total cost of a table scan on a data-only-locked table includes the logical and physical I/O for all pages in the table, plus the cost of logical and physical I/O for the OAM and allocation pages.
Figure 5-1 shows the pointers from OAM pages to allocation pages and from allocation pages to extents.
Figure 5-1: Sequence of pointers for OAM scans
The formula for computing the cost of an OAM scan with 2K I/O is:
OAM Scan Cost = (OAM_alloc_pages + Num_pages) * 18 + (OAM_alloc_pages + Num_pages)* 2
When large I/O can be used, the optimizer adds the cost of performing 2K I/O for the pages in the first extent of each allocation unit to the cost of performing 16K I/O on the pages in regular extents. The number of physical I/Os is the number of pages in the table, modified by a cluster adjustment that is based on the data page cluster ratio for the table.
See “How cluster ratios affect large I/O estimates” for more information on cluster ratios.
Logical I/O costs are one I/O per page in the table, plus the logical I/O cost of reading the OAM and allocation pages. The formula for computing the cost of an OAM scan with large I/O is:
OAM Scan Cost = OAM_alloc_pages * 18 + Pages in 1st extent * 18 + Pages in other extents / Pages per IO * Cluster adjustment * 18 + OAM_alloc_pages * 2 + Pages in table * 2
optdiag reports the number of pages for each of the needed values.
When a data-only-locked table contains forwarded rows, the I/O cost of reading the forwarded rows is added to the logical and physical I/O for a table scan.
See “Allpages-locked heap tables” on page 168 of the book Performance and Tuning: Basics for more information on row forwarding.
