Integration of buffer management and query optimization in relational database environment

Abstract

In today's relational database systems, query optimization and buffer management are generally treated independently. However, the query access plan contains information on data access patterns which can be useful hints to the buffer manager. Furthermore, the optimal access plans under different buffer sizes can be quite different. In this paper, an integrated approach to buffer management and query optimization is proposed and analyzed. The query strategy of all transaction types is simultaneously considered together with the buffer allocation strategy so as to optimize overall system performance. As the buffer allocation depends upon the buffer holding time or transaction response time which is determined by the buffer allocation and query optimization strategies, an optimization method combining a integer programming model with a queueing model applied iteratively is developed to capture this effect. To reduce the size of the optimization problem, a methodology based on the concept of buffer consumption is proposed to pre-analyze the query and substantially cut down the access plans to be considered. A detailed simulation is used to demonstrate the superiority of the integrated strategy as compared to other buffer management strategies based on working set and hot set.