Method for performing a query in a computer system to retrieve data from a database

• US 7,552,110 B2
• Filed: 09/22/2004
• Issued: 06/23/2009
• Est. Priority Date: 09/22/2003
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Hrle, Namik, Schuetzner, Johannes
• Primary Examiner(s): Breene; John E
• Assistant Examiner(s): Jami; Hares
• Application Number:
  US10/947,611
• Publication Number:
  US 20050065921A1
• Time in Patent Office: 1,735 Days
• Field of Search: 707/102, 707 1- 6, 707/100, 707/200
• US Class Current: 1/1
• CPC Class Codes: G06F 16/3346 using probabilistic model
• CPC Class Codes: G06F 16/3349 Reuse of stored results of previous queries
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

Presented is a method to perform a query to retrieve data from a database that is part of a computer system comprising a Data-Base Management System (DBMS) with an optimizer. A Query Execution Plan (QEP) is generated for the query. The QEP is assessed by considering statistical values about previous executions of the query according to the QEP to decide whether to choose the QEP or to generate a new QEP. Furthermore, the statistical values on previous executions of the query are exploited during query optimization. The query is executed according to the chosen QEP. For optimizing the execution of future queries, statistical values about the execution of the query are collected during execution.

Claims

1. A method of performing a query in a computer system to retrieve data from a database stored on the computer system which comprises a Data-Base Management System (DBMS) with an optimizer, the method comprising:

providing a processor and memory, wherein the memory stores one or more instructions for;

determining a Query Execution Plan (QEP) for the query;

assessing an access path of said QEP prior to each execution by applying a cost assessment formula using collected performance-related statement execution statistical values comprising performance data collected from one or more previous executions of said query according to said QEP;

a database process executing the query determining whether to choose said QEP or to generate a new QEP based on the results of the cost assessment formula using the collected performance-related statement execution statistical values;

generating the new QEP in response to determining that said QEP does not meet predetermined performance criteria;

creating one or more of new indexes, and Materialized Query Tables (MQTs) upon an assessment that the generated new QEP does not meet predetermined performance criteria;

executing said query according to a QEP selectively chosen using the collected performance-related statement execution statistical values and the new indexes;

collecting new performance-related statement execution statistical values during each execution of said query according to the chosen QEP, the new performance-related statement execution statistical values comprising performance data collected from the execution of said query and stored for optimizing an execution of future queries;

storing the collected new performance-related statement execution statistical values of said query according to said chosen QEP;

wherein the performance-related collected statement execution statistical values comprise performance data collected from one or more previous executions of said query accord to said QEP are accumulated in memory as long as the QEP is not invalidated for said query and wherein said collected statement execution statistical values are externalized in an initially created execution statistics history table when generating a new QEP for said query; and

wherein the performance-related collected statement execution statistical values collected during execution of the query comprises;

synchronous reads;

synchronous writes;

buffer reads;

buffer writes;

number of parallel groups;

record list failures;

I/O wait time;

wait time for refetched pages;

getpage operations;

number of examined rows;

number of qualifying rows;

number of get pages;

number of qualifying rows after application of aggregate functions; and

synchronous I/Os.

2. The method according to claim 1, wherein the optimizer comprises the cost assessment formula, the cost assessment formula controlling the generation of the QEP for said query by feeding the performance-related collected statement execution statistical values comprising performance data collected from one or more previous executions of said query according to said QEP into the cost assessment formula of the optimizer.

3. The method according to claim 1, wherein the collected performance-related statement execution statistical values comprising performance data collected from one or more previous executions of said query according to said QEP are persistently stored for use in identifying a trend and an effect of access path changes.

4. The method according to claim 1, wherein a statement text of said query and a QEP description are stored with corresponding collected performance-related statement execution statistical values in the execution statistics history table.

5. The method of claim 1, wherein the cost assessment formula comprises at least one of:

a ratio of number of rows examined to number of qualifying rows, a ratio of number of Getpages to number of qualifying rows, and a ratio of number of qualifying rows to number of qualifying rows after aggregate function.

6. A computer program product having executable instruction codes stored on a computer-readable storage medium are executing on a processor to retrieve data from a database, the executable instruction codes comprising:

determining a Query Execution Plan (QEP) for the query;

assessing an access path of said QEP prior to each execution by applying a cost assessment formula using collected performance-related statement execution statistical values comprising performance data collected from one or more previous executions of said query according to said QEP;

a database process executing the query determining whether to choose said QEP or to generate a new QEP based on the results of the cost assessment formula using the collected performance-related statement execution statistical values;

generating the new QEP in response to determining that said QEP does not meet predetermined performance criteria;

creating one or more of new indexes, and Materialized Query Tables (MQTs) upon an assessment that the generated new QEP does not meet predetermined performance criteria;

executing said query according to a QEP selectively chosen using the collected performance-related statement execution statistical values and the new indexes;

collecting new performance-related statement execution statistical values during each execution of said query according to the chosen QEP, the new performance-related statement execution statistical values comprising performance data collected from the execution of said query and stored for optimizing an execution of future queries;

storing the collected new performance-related statement execution statistical values of said query according to said chosen QEP;

wherein the performance-related collected statement execution statistical values comprise performance data collected from one or more previous executions of said query according to said QEP are accumulated in memory as long as the QEP is not invalidated for said query and wherein said collected statement execution statistical values are externalized in an initially created execution statistics history table when generating a new QEP for said query; and

wherein the performance-related collected statement execution statistical values collected during execution of the query comprises;

synchronous reads;

synchronous writes;

buffer reads;

buffer writes;

number of parallel groups;

record list failures;

I/O wait time;

wait time for refetched pages;

getpage operations;

number of examined rows;

number of qualifying rows;

number of get pages;

number of qualifying rows after application of aggregate functions; and

synchronous I/Os.

7. The computer program product of claim 6, wherein the optimizer comprises the cost assessment formula, the cost assessment formula controlling the generation of the QEP for said query by feeding the performance-related collected statement execution statistical values comprising performance data collected from one or more previous executions of said query according to said QEP into the cost assessment formula of the optimizer.

8. The computer program product of claim 6, wherein the collected performance-related statement execution statistical values comprising performance data collected from one or more previous executions of said query according to said QEP are persistently stored for use in identifying a trend and an effect of access path changes.

9. The computer program product of claim 6, wherein a statement text of said query and a QEP description are stored with corresponding collected performance-related statement execution statistical values in the execution statistics history table.

10. A system for performing a query in a computer system to retrieve data from a database stored on the computer system which comprises a Database Management System (DBMS) with an optimizer, the system comprising:

a DBMS comprising an optimizer for generating, assessing, and re-optimizing a Query Execution Plan (QEP) for a given query, the DBMS executing on a computer system comprising a processor and a computer-readable storage medium;

one or more instructions stored in the computer-readable storage medium and executable by the processor for;

determining a Query Execution Plan (QEP) for the query;

assessing an access path of said QEP prior to each execution by applying a cost assessment formula using collected performance-related statement execution statistical values comprising performance data collected from one or more previous executions of said query according to said QEP;

a database process executing the query determining whether to choose said QEP or to generate a new QEP based on the results of the cost assessment formula using the collected performance-related statement execution statistical values;

generating the new QEP in response to determining that said QEP does not meet predetermined performance criteria;

creating one or more of new indexes, and Materialized Query Tables (MQTs) upon an assessment that the generated new QEP does not meet predetermined performance criteria;

executing said query according to a QEP selectively chosen using the collected performance-related statement execution statistical values and the new indexes;

collecting new performance-related statement execution statistical values during each execution of said query according to the chosen QEP, the new performance-related statement execution statistical values comprising performance data collected from the execution of said query and stored for optimizing an execution of future queries;

storing the collected new performance-related statement execution statistical values of said query according to said chosen QEP;

wherein the performance-related collected statement execution statistical values comprise performance data collected from one or more previous executions of said query according to said QEP are accumulated in memory as long as the QEP is not invalidated for said query and wherein said collected statement execution statistical values are externalized in an initially created execution statistics history table when generating a new QEP for said query; and

wherein the performance-related collected statement execution statistical values collected during execution of the query comprises;

synchronous reads;

synchronous writes;

buffer reads;

buffer writes;

number of parallel groups;

record list failures;

I/O wait time;

wait time for refetched pages;

getpage operations;

number of examined rows;

number of qualifying rows;

number of get pages;

number of qualifying rows after application of aggregate functions; and

synchronous I/Os.

11. The system of claim 10, wherein the optimizer comprises the cost assessment formula, the cost assessment formula controlling the generation of the QEP for said query by feeding the performance-related collected statement execution statistical values comprising performance data collected from one or more previous executions of said query according to said QEP into the cost assessment formula of the optimizer.

12. The system of claim 10, wherein the collected performance-related statement execution statistical values comprising performance data collected from one or more previous executions of said query according to said QEP are persistently stored for use in identifying a trend and an effect of access path changes.

13. The system of claim 10, wherein a statement text of said query and a QEP description are stored with corresponding collected performance-related statement execution statistical values in the execution statistics history table.