When Not to Reorganize
When statistics show some disorganization, is it time to automatically trigger reorganization? What if the application processing is actually maintaining peak performance? If space allocation is adequate, reorganization of a well-performing object is a waste of time and resources.
Asynchronous I/O vs. Synchronous I/O
DB2 prefetches pages into the buffer pool. As soon as DB2 recognizes the least bit of sequential access, it begins to fetch pages from disk so they’ll be in buffer pools before your application needs them—to dramatically reduce I/O wait times and improve application performance. This is known as asynchronous I/O because it happens in parallel with (indeed, ahead of) application processing and is especially effective for workloads that scan lots of data. Asynchronous I/O is sensitive to data being organized and kept in cluster sequence. Maintaining current statistics and triggering reorganization based on threshold exceptions supports this scan-oriented workload well.
When an application does a more discrete or random access, it will probably be serviced by synchronous I/O, which occurs when an application demands a specific row. Synchronous I/O is more tolerant of data disorganization— it will probe an index, obtain the information to directly access the data, then retrieve it (assuming the data isn’t already in the buffer pool).
DB2 can use PREFETCH differently, depending on the situation:
• With viable indexes, the DB2 Optimizer will try to use PREFETCH to asynchronously read in the data pages.
• If it can determine that sequential processing is reasonable when the access path is determined, DB2 will call for SEQUENTIAL PREFETCH.
• If it identifies the need for many specific records that aren’t sequentially located, DB2 may call for Row ID (RID)-based LIST PREFETCH.
• Even if the access path calls for random access (which involves synchronous I/O), run-time monitoring may invoke DYNAMIC PREFETCH if the pages requested appear to be even loosely sequential.