Monitoring and Adjustment of Hash Table Size with Adaptive Query Processing

In IBM® i 7.6, Adaptive Query Processing (AQP) will be utilized to monitor and adjust hash table sizes during and after query execution. By dynamically adjusting hash table sizes, AQP will correct potential performance degradations due to undersized hash tables.

The size of a hash table is estimated by the query optimizer before execution. If the size is smaller than the number of elements inserted into the hash table, significant performance issues may arise. To address possible performance degradations, AQP will monitor the size and population of all hash tables. If the number of rows contained within a hash table substantially exceeds the size, AQP will increase the size of the hash table. If detected during execution, the query will be restarted. If detected after execution, any cursors utilizing the plan will be hard closed. In both scenarios, any subsequent executions of the query will utilize the increased hash table size.