Measure the Amount of Overflow
You can monitor overflow using Director, VDBA, or an SQL statement.
In Director, right-click a table in the Instance Explorer, and then select Properties.
In VDBA, select a table or secondary index in the Database Object Manager window, and click the Pages tab.
In SQL, you can monitor overflow with the HELP TABLE statement. For more information, see the SQL Reference Guide.
For tables, overflow data is displayed in red in the pie chart, as indicated in the legend. Heap tables are considered as one main page, with an overflow chain attached to the main page. For B-tree tables, overflow occurs only at the leaf level and only with duplicate keys.
The iitables catalog (a view into the iirelation catalog) includes one row for each table in the database. It contains pertinent information for evaluating overflow.
For example, the following query results in the information shown in the table:
select table_name, storage_structure,
number_pages, overflow_pages
from iitables
The above figures are approximate; they are updated only when they change by a certain percentage (5%) to prevent performance degradation by continuously updating these catalogs. Also, if transactions that involve many new pages are backed out during a recovery, the page counts cannot be updated. Page counts are guaranteed to be exact only after modification.
In evaluating overflow, if the number of overflow pages is greater than 10-15% of the number of data pages, expect performance degradation. Overflow must be regularly monitored to ensure that performance does not degrade as rows are appended to tables.