Adjusting an amount of data logged for a query based on a change to an access plan

• US 7,493,304 B2
• Filed: 11/12/2004
• Issued: 02/17/2009
• Est. Priority Date: 11/12/2004
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: International Business Machines Corporation
• Inventors: Day, Paul Reuben, Muras, Brian Robert, Ryg, Anne Marie
• Primary Examiner(s): Chace; Christian P.
• Assistant Examiner(s): Hoang; Son T
• Application Number:
  US10/988,354
• Publication Number:
  US 20060106786A1
• Time in Patent Office: 1,558 Days
• Field of Search: 707/2, 707/3, 707/102
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2455 Query execution
• 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/99943 Generating database or data structure, e.g. via user interface

Abstract

In an embodiment, a problem record associated with a query is saved to a log if the query encountered a problem. The amount of data in the record is increased if an access plan associated with the query has changed within a threshold amount of time and decreased if the access plan has not changed. In various embodiments, the problem is that the query executes longer than a historical average for the query, the query executes longer than a threshold, or the query encounters a functional error. In an embodiment, a benchmark record is also saved to the log, the problem record is compared to the benchmark record, and the difference is presented. In various embodiments, an oldest record is periodically removed from the log, or an oldest record is removed from the log if the associated query has more than a threshold number of records.

Claims

1. A computer-implemented method comprising:

receiving a query;

performing the query against a database based on an access plan, wherein the access plan indicates steps that primitives take to execute the query against a database;

if benchmarking is enabled, determining whether the query already has a benchmark record in a log, wherein the benchmark record comprises first performance data;

if the query already has the benchmark record in the log, determining whether second performance data associated with the performing the query indicates superior performance than the first performance data;

if the second performance data associated with the performing the query indicates superior performance than the first performance data, deleting the benchmark record from the log and adding a new benchmark record to the log, wherein the new benchmark record comprises the second performance data;

if the second performance data associated with the performing the query does not indicate superior performance than the first performance data, refraining from performing the deleting the benchmark record and the adding the new benchmark record;

if the query does not already have the benchmark record in the log, adding the new benchmark record to the log, wherein the new benchmark record comprises the second performance data associated with the performing the query;

if benchmarking is not enabled, determining whether the query runs longer than a historical average for the query;

if the query runs longer than the historical average for the query, determining whether the access plan has been changed within a threshold amount of time,

if the access plan has been changed within the threshold amount of time, creating a problem record in the log and increasing an amount of data saved to the problem record;

if the access plan has not been changed within the threshold amount of time, creating the problem record in the log and decreasing the amount of the data saved to the problem record;

if the query runs longer than the historical average for the query, determining whether the query has more than a threshold number of the problem records in the log;

if the query has more than the threshold number of the problem records in the log, removing an oldest record that is associated with the query from the log;

if the query does not have more than the threshold number of the problem records in the log, finding the new benchmark record in the log, comparing the new benchmark record to the problem record, and presenting a difference based on the comparing, wherein the presenting the difference based on the comparing further comprises presenting the difference between the second performance data in the new benchmark record and the data in the problem record; and

if the query does not run longer than the historical average for the query, refraining from performing the determining whether the access plan has been changed within the threshold amount of time.

2. A computer-readable storage medium encoded with computer instructions for causing a computer to execute a method comprising:

receiving a query;

performing the query against a database based on an access plan, wherein the access plan indicates steps that primitives take to execute the query against a database;

if benchmarking is enabled, determining whether the query already has a benchmark record in a log, wherein the benchmark record comprises first performance data;

if the query already has the benchmark record in the log, determining whether second performance data associated with the performing the query indicates superior performance than the first performance data;

if the second performance data associated with the performing the query indicates superior performance than the first performance data, deleting the benchmark record from the log and adding a new benchmark record to the log, wherein the new benchmark record comprises the second performance data;

if the second performance data associated with the performing the query does not indicate superior performance than the first performance data, refraining from performing the deleting the benchmark record and the adding the new benchmark record;

if the query does not already have the benchmark record in the log, adding the new benchmark record to the log, wherein the new benchmark record comprises the second performance data associated with the performing the query;

if benchmarking is not enabled, determining whether the query runs longer than a historical average for the query;

if the query runs longer than the historical average for the query, determining, whether the access plan has been changed within a threshold amount of time;

if the access plan has been changed within the threshold amount of time, creating a problem record in the log and increasing an amount of data saved to the problem record;

if the access plan has not been changed within the threshold amount of time, creating the problem record in the log and decreasing the amount of the data saved to the problem record;

if the query runs longer than the historical average for the query, determining whether the query has more than a threshold number of the problem records in the log;

if the query has more than the threshold number of the problem records in the log, removing an oldest record that is associated with the query from the log;

if the query does not have more than the threshold number of the problem records in the log, finding the new benchmark record in the log, comparing the new benchmark record to the problem record, and presenting a difference based on the comparing, wherein the presenting the difference based on the comparing further comprises presenting the difference between the second performance data in the new benchmark record and the data in the problem record; and

if the query does not run longer than the historical average for the query, refraining from performing the determining whether the access plan has been changed within the threshold amount of time.

3. A computer system comprising:

a processor; and

a computer-readable storage device encoded with computer instructions for causing the processor to execute a method comprising;

receiving a query;

performing the query against a database based on an access plan, wherein the access plan indicates steps that primitives take to execute the query against a database,

if benchmarking is enabled, determining whether the query already has a benchmark record in a log, wherein the benchmark record comprises first performance data,

if the query already has the benchmark record in the log, determining whether second performance data associated with the performing the query indicates superior performance than the first performance data,

if the second performance data associated with the performing the query indicates superior performance than the first performance data, deleting the benchmark record from the log and adding a new benchmark record to the log, wherein the new benchmark record comprises the second performance data,

if the second performance data associated with the performing the query does not indicate superior performance than the first performance data, refraining from performing the deleting the benchmark record and the adding the new benchmark record,

if the query does not already have the benchmark record in the log, adding the new benchmark record to the log, wherein the new benchmark record comprises the second performance data associated with the performing the query,

if benchmarking is not enabled, determining whether the query runs longer than a historical average for the query,

if the query runs longer than the historical average for the query, determining whether the access plan has been changed within a threshold amount of time,

if the access plan has been changed within the threshold amount of time, creating a problem record in the log and increasing an amount of data saved to the problem record,

if the access plan has not been changed within the threshold amount of time, creating the problem record in the log and decreasing the amount of the data saved to the problem record,

if the query runs longer than the historical average for the query, determining whether the query has more than a threshold number of the problem records in the log,

if the query has more than the threshold number of the problem records in the log, removing an oldest record that is associated with the query from the log,

if the query does not have more than the threshold number of the problem records in the log, finding the new benchmark record in the log, comparing the new benchmark record to the problem record, and presenting a difference based on the comparing, wherein the presenting the difference based on the comparing further comprises presenting the difference between the second performance data in the new benchmark record and the data in the problem record, and

if the query does not run longer than the historical average for the query, refraining from performing the determining whether the access plan has been changed within the threshold amount of time.