Warm Cache Costing – A Feedback Optimization Technique for Buffer Pool Aware Costing

Most modern RDBMS depend on the query processing optimizer’s cost model to choose the best execution plan for a given query. Since the physical IO (PIO) is a costly operation to execute, it nat-urally has an important weight in RDBMS classical cost models, which assume that the data is disk-resident and does not fit in the available main memory. However, this assumption is no longer true with the advent of cheap large main memories. In this paper, we discuss the importance of considering the buffer-cache occupancy during optimization and propose the Warm Cache Costing (WCC) model as a new technique for buffer-pool aware query optimization. The WCC-model is a novel feedback optimiza-tion technique, based on the execution statistics by learning PIO-compensation (PIOC) factors. The PIOC factor defines the average percentage of a table which is cached in the buffer pool. These PIOC factors are used in subsequent query optimizations to better estimate the PIO, thus leading to better plans. These techniques have been implemented in Sybase Adaptive Server Enterprise (ASE) database system. We have observed that they pro-vide considerable improvements in query timings, in Decision Sup-port environments and with almost negligible regression(if any) in other environments. This model enjoys the advantage of requiring no change to the buffer manager or other modules underlying the query processing layer and therefore is easy to implement. Also, since this model is part of an extensive feedback optimization archi-tecture, other techniques using feedback optimization framework can be plugged in easily. 1