×

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
First Claim
Patent Images

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.

View all claims
  • 1 Assignment
Timeline View
Assignment View
    ×
    ×