Hash Join and Hash Aggregation Improvements
Improvements to hash join and hash aggregation enable faster queries and better concurrent query capability. Environments that will benefit are those with moderate to heavy concurrency (where memory usage is a potential problem), or in which the hash join or hash aggregation cannot fit in memory and is therefore spilled to disk before completing.
This feature adds five new configuration parameters to the DBMS Server component:
qef_hash_rbsize
The size in bytes of a read buffer for a hash operation (join or aggregation). There is one read buffer per hash operation, so we recommend relatively large values.
Default: 128 KB
qef_hash_wbsize
The size in bytes of a write buffer for a hash operation (join or aggregation). There are many write buffers per hash operation, and in the hash join case, the write buffer size interacts with the number of hash “buckets.” Larger buffers can mean fewer (and therefore larger) buckets. Smaller buffers mean less room for rows in memory and more spillage. Installations must balance spill write efficiency (large qef_hash_wbsize) against memory usage and the ability to fit many buckets in memory (small qef_hash_wbsize).
Default: 16 KB
qef_hash_cmp_threshold
The minimum size of the compressible (non-key) part of a row to be considered for run-length compression. Run-length compression allows more hash-join rows to fit in memory, and reduces the size of hash join or hash aggregation spill to disk, at the expense of some extra CPU overhead.
Default: 128 bytes
qef_hashjoin_min
The minimum allocation, in bytes, for a hash join. Most installations do not need to change qef_hashjoin_min unless the optimizer underestimates the size of hash joins, causing excessive spillage.
Default: 0 (no minimum)
qef_hashjoin_max
The maximum allocation, in bytes, for a hash join. Most installations do not need to change qef_hashjoin_max unless the optimizer frequently over-estimates the size of hash joins, taking memory away from needier hash joins.
Default: 0 (which means use qef_hash_mem)
The qef_hash_rbsize and qef_hash_wbsize parameters may need to be changed to suit your environment. The qef_hashjoin_min and qef_hashjoin_max parameters will rarely need to be changed, since they are for preventing problems in extreme situations.
The Ingres upgrade process adds these parameters to an existing config.dat. If for some reason any parameters are missing from config.dat, the default values are used.
For details about the parameters, see the online help for Configuration-By-Forms (or the equivalent visual tool).