A data threshold detection approach to predicting future query behavior
Douglas D. Dankel, Jeff Magnusson · 2009
For a database to function efficiently, the query optimizer must be presented with an efficient set of indexes for the query workload. Often inefficient index sets are not noticed until they have a substantial negative impact on the database processes. In an active database, the sizes of tables and distributions of data constantly grow at different relative rates. Traditionally, the access plans produced by the cost-based query optimizer change when two related data distributions grow relative to one another past some threshold. These “data thresholds” are significant because they represent the point at which previously optimal indexes become suboptimal. Due to the size and complexity of today’s database systems, many DBAs rely on autonomic tuning tools to assist them in determining optimal index sets for the database workload. We propose an extension to these autonomic tuning tools that will allow DBAs to estimate future performance of queries and which will automatically recommend optimal index sets for the estimated future data distributions. This is accomplished by recording and analyzing changes in the database statistics captured by the DBMS over time. Forecasts of future values of these statistics are computed and used to create an alternate system catalog from which to compile queries. This effectively tricks the query optimizer into optimizing queries assuming the values of the forecasted data distributions. Current methods of deriving optimal index sets for a query workload can then be applied to make optimal index predictions for the future. In addition, we show that the extension is useful as a user-space tool for estimating query scaling and performance.