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 naturally 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 optimization 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 provide considerable improvements in query timings, in Decision Support 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 architecture, other techniques using feedback optimization framework can be plugged in easily.

The Sybase ASE RDBMS version 15 was subject to major enhancements, including semantic partitions and a full QP rewrite. The new ASE QP supports horizontal and vertical parallel processing over semantically partitioned tables, and many other modern QP techniques, as cost-based eager aggregation and cost-based join relocation DQP. In the new query optimizer, the ordering, distinctness, aggregation, partitioning, and DQP optimizations were based on a common framework: plan fragment equivalence classes and logical properties. Our main outcomes are a) an eager enforcement policy for ordering, partitioning and DQP location; b) a distinctness and aggregation optimization policy, opportunistically based on the eager ordering enforcement, and which has an optimization-time computational complexity similar to join processing; c) support for the user to force all of the above optimizer decisions, still guaranteeing a valid plan, based on the Abstract Plan technology. We describe the implementation of this solution in the ASE 15 optimizer. Finally, we give our experimental results: the generation of such complex plans comes with a small increase of the optimizer's SS size, hence within an acceptable optimization time; at execution, we have obtained performance improvements of orders of magnitude for some queries.