Integration of buffer management and query optimization in relational database environment

Abstract

In today’s relational database systems, query optimi7alion and hufTer management are generally treated indrpcndently. I Iowever, the query access plan con(ains information on data access patterns which can he useful hints to the huffcr matIager. T;urlhermore, the optimal access plans under dilTerent huffcr sizes can be quite difTcrent. In this paper, an integrated approach to buffer management and query optimization is proposed and analyzed. ‘I he query strategy of all transaction types is simultaneously considered together willt the hulTcr allocalion strategy so as to optimbe overall system performance. As the buffer allocation depends upon (he hulTer holding time or transaction response time whicft iz dcfrrn~ir~rd h) the buffer allocation and query optimization slrnlcgirs, ;III oplimization method combining a integer programming model with a queueing model applied iteratively is developed lo capture this effect. To reduce the size of the optimi7atinn problem, a methodology hascd on the concept of hulrcr consumplion is proposed to pre-analyze the query and suhstacltially cut down the access plans to be considered. A detailed simulation is used to demonstrate fhe superiority of ihe intcgrated strategy as compared to other hulTcr tnannpenrrnt strategies based on working set and hot set.