Autonomically generating a query implementation that meets a defined performance specification

Abstract

A database query optimizer allows specifying a performance specification for a query. When an access plan is created, the actual performance for executing the query using the access plan is determined and compared to the performance specification for the query. If the actual performance does not satisfy the performance specification for the query, the query optimizer autonomically generates a different access plan to see if the performance improves. If an access plan that meets the performance specification is generated, the new access plan is stored in the access plan cache for the query. If no access plan can be generated that meets the performance specification for the query, the access plan that provided the best performance is selected and stored in the access plan cache for the query.

Description

CROSS-REFERENCE TO PARENT APPLICATION[0001]This patent application is a continuation of a “Apparatus and Method for Autonomically Generating a Query Implementation that Meets a Defined Performance Specification,” U.S. Ser. No. 10 / 860,402 filed on Jun. 3, 2004, which is incorporated herein by reference.BACKGROUND OF THE INVENTION[0002]1. Technical Field[0003]This invention generally relates to computer systems, and more specifically relates to apparatus and methods for optimizing the run-time performance of a database query.[0004]2. Background Art[0005]Since the dawn of the computer age, computers have evolved and become more and more powerful. In our present day, computers have become indispensable in many fields of human endeavor including engineering design, machine and process control, information storage and retrieval, and office computing. One of the primary uses of computers is for information storage and retrieval.[0006]Database systems have been developed that allow a comput...

AI Technical Summary

Benefits of technology

The invention is a system that optimizes the performance of database queries. It allows a user to specify the performance requirements for a query and create an access plan to execute the query. If the actual performance does not meet the requirements, the system automatically generates a new access plan until a suitable one is found. The system stores the best performing access plan in a cache for future reference. This invention improves the efficiency and speed of database queries.

Problems solved by technology

Note, however, that the performance of the query may be fine at the point in time when the access plan was generated, but due to changes in the database, software, etc., the same access plan may experience a marked slowdown in query performance.

In addition, because the initial access plan is based on an estimate of performance instead of on actual execution time, the initial access plan may not be a very efficient implementation of the query.

In the prior art, there is no way for the database optimizer to detect when an access plan performs poorly.

Without a way to specify desired performance for a query and autonomically generate a new access plan when the current access plan no longer satisfies the desired performance, the database industry will continue to suffer from undue performance penalties by continually reusing access plans that provide poor performance in executing a query.

Images