The FREEPAGE and PCTFREE clauses can help improve the performance of updates and inserts by allowing free space to exist on table spaces. Performance improvements include improved access to the data through the better clustering of data, faster inserts, fewer row overflows, and a reduction in the number of REORGs required.
Some tradeoffs include an increase in the number of pages, fewer rows per I/O, less efficient use of buffer pools, and more pages to scan. Therefore, it is important to achieve a good balance for each individual table space and index space when deciding on free space. The balance depends on the processing requirements of each table space or index space. When inserts and updates are performed, DB2 uses the available free space, and by doing so, it can keep records in clustering sequence as much as possible. When the free space is used up, the records must be located elsewhere; when this happens, performance can begin to suffer.
Read-only tables do not require any free space, and tables with a pure insert-at-end strategy (append processing) generally do not require free space. Exceptions to this guideline would be tables with VARCHAR columns and tables using compression that are subject to updates. When DB2 attempts to maintain clustering during inserting and updating, it searches nearby for free space or free pages for the row. If DB2 does not find this space, it searches the table space for a free place to put the row before extending a segment or a dataset. Indications of this activity are as follows:
When this activity happens, perform a REORG and reevaluate your free space allocations.
|
Copyright © 2014 CA Technologies.
All rights reserved.
|
|