Method and system for data mining for automatic query optimization

Abstract

A database monitor tracks performance statistics and information about the execution of different SQL statements. A query optimizer benefits from these statistics when generating an access plan. In particular, the query optimizer, upon receiving an SQL statement, searches the records of the database monitor for similar SQL statements that have previously been executed. As part of determining the best access plan for the current SQL statement, the query optimizer considers the information retrieved from the database monitor. In this way, the access plan that is generated can automatically be tuned based on empirical performance evidence.

1 . A method for generating an access plan, the method comprising the steps of:
 maintaining in non-volatile memory a respective set of past performance statistics for each of a plurality of previously executed database queries, wherein the sets of past performance statistics are associated with respective database queries, and wherein each set of past performance statistics includes one or more of number of rows updated, inserted, or deleted by a respective database query, number of rows fetched by a respective database query, elapsed time for a respective database query, estimated completion time for a respective database query, estimated I/O operations for a respective database query, actual I/O operations for a respective database query or host variables for a respective database query;   in response to receiving a current database query, identifying a particular set of past performance statistics from among the sets of past performance statistics for the plurality of previously executed database queries that relates to the current database query; and   generating and storing an access plan for the current database query based at least in part on the particular set of past performance statistics identified in response to receiving the current database query.   
     
     
         2 . The method of  claim 1 , wherein the sets of past performance statistics are collected by a database monitor. 
     
     
         3 . The method of  claim 1 , wherein the sets of past performance statistics comprise a database table. 
     
     
         4 . The method of  claim 3 , wherein the step of identifying further includes the step of:
 querying the database table for records similar to the current database query.   
     
     
         5 . The method of  claim 1 , wherein the step of generating further includes the step of:
 selecting the access plan from among a plurality of access plans that would implement the current database query.   
     
     
         6 . A method for generating an access plan, the method comprising the steps of:
 maintaining in non-volatile memory a respective set of past performance statistics for each of a plurality of previously executed database queries, wherein each set of past performance statistics includes one or more of number of rows updated, inserted, or deleted by a respective database query, number of rows fetched by a respective database query, elapsed time for a respective database query, estimated completion time for a respective database query, estimated I/O operations for a respective database query, actual I/O operations for a respective database query or host variables for a respective database query;   in response to receiving a current database query, identifying a particular set of past performance statistics corresponding to a previously executed database query similar to the current database query;   analyzing performance of an access plan identified within the particular set of past performance statistics identified in response to receiving the current database query; and   generating and storing a current access plan for the current database query based at least in part on the access plan.   
     
     
         7 . The method of  claim 6 , wherein a JOIN order in the access plan is used when generating the current access plan. 
     
     
         8 . The method of  claim 6 , wherein index information from the access plan is used when generating the current access plan. 
     
     
         9 . The method of  claim 6 , wherein the access plan is used as the current access plan. 
     
     
         10 . The method of  claim 6 , wherein the sets of past performance statistics are collected by a database monitor. 
     
     
         11 . The method of  claim 6 , wherein the step of identifying further includes the steps of:
 identifying a file referenced by the database query; and   determining if the file was re-organized after execution of the access plan.   
     
     
         12 . The method of  claim 11 , wherein the step of determining includes the step of:
 determining if one or more indices related to the file have been rebuilt subsequent to execution of the access plan.   
     
     
         13 . The method of  claim 6 , wherein the step of analyzing includes the step of:
 determining if an execution time of the access plan exceeded an estimated execution time.   
     
     
         14 . The method of  claim 13 , further comprising the step of:
 increasing the estimated execution time for the access plan to a new estimated execution time, if the execution time exceeded the estimated execution time.   
     
     
         15 . A method for generating an access plan, the method comprising the steps of:
 maintaining in non-volatile memory a respective set of past performance statistics for each of a plurality of previously executed database queries, wherein each set of past performance statistics includes one or more of number of rows updated, inserted, or deleted by a respective database query, number of rows fetched by a respective database query, elapsed time for a respective database query, estimated completion time for a respective database query, estimated I/O operations for a respective database query, actual I/O operations for a respective database query or host variables for a respective database query;   in response to receiving a current database query, identifying a current access plan to implement the current database query;   in response to receiving the current database query, identifying a particular set of past performance statistics corresponding to a previously executed database query that used the current access plan;   analyzing performance of the previously executed database query using the particular set of past performance statistics identified in response to receiving the current database query;   deciding whether to re-use the current access plan for the current database query based on the analysis of the performance of the previously executed database query; and   storing the decision of whether to re-use the current access plan for the current database query.   
     
     
         16 . The method of  claim 15 , further comprising the step of:
 forcing a re-optimization to generate a new access plan based on the analysis of the performance of the previously executed database query.   
     
     
         17 . The method of  claim 15 , wherein the sets of past performance statistics are collected by a database monitor. 
     
     
         18 . An apparatus comprising:
 at least one processor;   a memory coupled with the at least one processor; and   a program code residing in memory and executed by the at least one processor, the program code configured to:
 maintain in non-volatile memory a respective set of past performance statistics for each of a plurality of previously executed database queries, wherein the sets of past performance statistics are associated with respective database queries, wherein each set of past performance statistics includes one or more of number of rows updated, inserted, or deleted by a respective database query, number of rows fetched by a respective database query, elapsed time for a respective database query, estimated completion time for a respective database query, estimated I/O operations for a respective database query, actual I/O operations for a respective database query or host variables for a respective database query; 
 in response to receiving a current database query, identify a particular set of past performance statistics from among the sets of past performance statistics for the plurality of previously executed database queries that relates to the current database query; and 
 generate and store an access plan for the current database query based at least in part on the particular set of past performance statistics identified in response to receiving the current database query. 
   
     
     
         19 . The apparatus of  claim 18 , further comprising:
 a database of a respective set of past performance statistics for each of a plurality of previously executed database queries.   
     
     
         20 . The apparatus of  claim 18  further comprising:
 a database monitor configured to maintain a database of a respective set of past performance statistics for each of a plurality of previously executed database queries. 
 
     
     
         21 . The apparatus of  claim 20 , wherein the program code is further configured to search the database for records similar to the current database query. 
     
     
         22 . The apparatus of  claim 18 , wherein the program code is further configured to select the access plan from a plurality of access plans that would implement the current database query. 
     
     
         23 . A program product, comprising:
 a program code configured upon execution to:
 maintain in non-volatile memory a respective set of past performance statistics for each of a plurality of previously executed database queries, wherein the sets of past performance statistics are associated with respective database queries, wherein each set of past performance statistics includes one or more of number of rows updated, inserted, or deleted by a respective database query, number of rows fetched by a respective database query, elapsed time for a respective database query, estimated completion time for a respective database query, estimated I/O operations for a respective database query, actual I/O operations for a respective database query or host variables for a respective database query; 
 in response to receiving a current query, identify a particular set of past performance statistics from among the sets of past performance statistics for the plurality of previously executed database queries that relates to the current database query; and 
 generate and store an access plan for the current database query based at least in part on the particular set of past performance statistics identified in response to receiving the current database query; and 
   a computer-readable storage medium storing the program code.