System and method for performing a query in a computer system to retrieve data from a database

• US 20050065921A1
• Filed: 09/22/2004
• Published: 03/24/2005
• Est. Priority Date: 09/22/2003
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Hrle, Namik, Schuetzner, Johannes
• Granted Patent: US 7,552,110 B2
• Time in Patent Office: Days
• 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

A computer system and a corresponding method perform a query to retrieve data from a database. The computer system comprises 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:

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

assessing said QEP by considering statistical values about previous executions of said query according to said QEP;

determining whether to choose said QEP or to generate a new QEP;

executing said query according to the generated QEP; and

collecting statistical values during execution for optimizing an execution of future queries.

2. The method according to claim 1, wherein the generated new QEP is further assessed by considering corresponding execution statistics.

3. The method according to claim 2, further comprising creating any one or more new indexes or Materialized Query Tables (MQTs) if an assessment of the generated new QEP does not promise a predetermined performance.

4. The method according to claim 1, wherein assessing said QEP for a query comprises feeding the collected statistical values about previous executions of said query according to said QEP into an assessment formula.

5. The method according to claim 4, wherein the optimizer comprises a cost formula that controls the generation of the best QEP for a query by feeding the collected statistical values about previous executions of said query according to said QEP into the cost formula of the optimizer.

6. The method according to claim 5, wherein the statistical values about the execution of a query according to a QEP are persistently stored to identify a trend and an effect of access path changes.

7. The method according to claim 6, wherein the statistical values about the execution of a query according to a QEP are collected in main memory as long as the QEP is not invalidated for said query and wherein said accumulated statistical values are externalized in an initially created execution statistics history table when generating a new QEP for said query.

8. The method according to claim 7, wherein a statement text of said query and a QEP description are stored together with corresponding accumulated statistical values in the execution statistics history table.

9. The method according to claim 7, wherein the statistical values collected during execution of the query comprise any one or more of:

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.

10. A computer system for performing a query to retrieve data from a database, comprising:

a Data-Base Management System (DBMS) with an optimizer for generating, assessing, and reoptimizing a Query Execution Plan (QEP) for a given query;

a memory for storing once generated QEPs for given queries;

an instrumentation facility for collecting statistical values about an execution of queries according to once generated QEPs;

a runtime engine to initiate the execution of queries according to respectively generated QEPs; and

a logic performed by the computer system,

for generating a QEP for the given query;

for exploiting statement execution statistics during the generation of a QEP;

for assessing a generated QEP by considering statistical values about previous executions of said query according to said QEP;

for determining whether to choose said QEP or to generate a new QEP;

for executing said query according to the generated QEP; and

for collecting statistical values during execution for optimizing an execution of future queries.

11. The computer system according to claim 10, wherein the generated new QEP is further assessed by considering corresponding execution statistics.

12. The computer system according to claim 11, further comprising logic for creating any one or more new indexes or Materialized Query Tables (MQTs) if an assessment of the generated new QEP does not promise a predetermined performance.

13. The computer system according to claim 12, wherein the assessment of a QEP for the query feeds the collected statistical values about previous executions of said query according to said QEP into an assessment formula.

14. The computer system according to claim 13, wherein the optimizer comprises a cost formula that controls the generation of a QEP for a query by feeding the collected statistical values about previous executions of said query according to said QEP into the cost formula of the optimizer.

15. The computer system according to claim 14, further comprising a logic for persistently storing the statistical values about the execution of a query according to a QEP, in order to identify a trend and an effect of access path changes.

16. The computer system according to claim 15, further comprising a logic for collecting the statistical values about the execution of a query according to a QEP in a main memory as long as the QEP is not invalidated for said query and for externalizing said accumulated statistical values in an initially created execution statistics history table when generating a new QEP for said query.

17. A computer program product having executable instruction codes to retrieve data from a database, comprising:

a first set of instruction codes for generating, assessing, and reoptimizing a Query Execution Plan (QEP) for a given query;

a memory for storing once generated QEPs for given queries;

a second set of instruction codes for collecting statistical values about an execution of queries according to once generated QEPs;

a third set of instruction codes for initiating the execution of the queries according to respectively generated QEPs; and

a fourth set of instruction code for implementing the following functions;

generating a QEP for the given query;

exploiting statement execution statistics during the generation of a QEP;

assessing a generated QEP by considering statistical values about previous executions of said query according to said QEP;

determining whether to choose said QEP or to generate a new QEP;

executing said query according to the generated QEP; and

collecting statistical values during execution for optimizing an execution of future queries.

18. The computer program product according to claim 17, wherein the generated new QEP is further assessed by considering corresponding execution statistics.

19. The computer program product according to claim 18, further comprising a fifth set of instruction codes for creating any one or more new indexes or Materialized Query Tables (MQTs) if an assessment of the generated new QEP does not promise a predetermined performance.

20. The computer program product according to claim 19, wherein the assessment of a QEP for the query feeds the collected statistical values about previous executions of said query according to said QEP into an assessment formula.

21. The computer program product according to claim 20, wherein the first set of instruction codes comprises a cost formula that controls the generation of a QEP for a query by feeding the collected statistical values about previous executions of said query according to said QEP into the cost formula of the optimizer.

22. The computer program product according to claim 21, further comprising a sixth set of instruction codes for persistently storing the statistical values about the execution of a query according to a QEP, in order to identify a trend and an effect of access path changes.

23. The computer program product according to claim 22, further comprising a seventh set of instruction codes for collecting the statistical values about the execution of a query according to a QEP in a main memory as long as the QEP is not invalidated for said query and for externalizing said accumulated statistical values in an initially created execution statistics history table when generating a new QEP for said query.