Asynchronous task for energy cost aware database query optimization

• US 7,523,094 B1
• Filed: 03/16/2008
• Issued: 04/21/2009
• Est. Priority Date: 03/16/2008
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: International Business Machines Corporation
• Inventors: Hu, Wei, Bestgen, Robert Joseph, Kethireddy, Shantan, Thiemann, Ulrich, Passe, Andrew Peter
• Primary Examiner(s): Cottingham; John
• Assistant Examiner(s): WILSON, KIMBERLY LOVEL
• Application Number:
  US12/049,343
• Time in Patent Office: 401 Days
• Field of Search: None
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

This is an example of a new method for optimization of database queries which uses an asynchronous task which keeps the most current status of the database objects by directly interfacing the existing storage management system. This asynchronous task in combination with query plan and cache data is able to aid in detecting and choosing “warm” query costing alternatives. The cold/warm status information of the asynchronous task helps query optimizer to determine: if it is suitable to use “warm” costing for a new query optimization; validate previous decisions to use a “warm”-optimized query plan, when it comes to potentially reuse and run with such a plan again; decide whether it'"'"'s worthwhile to still cache such a plan in the plan cache.

Claims

1. A method of optimizing a first access plan for a first database query, said method comprising:

querying a plan cache;

wherein said plan cache comprises one or more cached queries;

determining one or more cache-query database objects referenced by any of said one or more cached queries;

asynchronously querying, monitoring, and recording a first status for a first cache-query database object of said one or more cache-query database objects via a storage management interface;

wherein if said first cache-query database object is cached and accessing said first cache-query database object consumes a logical access to a cached data, then said first status is warm-status type;

wherein if said first cache-query database object is not cached and accessing said first cache-query database object consumes a physical disk drive access, then said first status is cold-status type;

cost-based optimizing said first access plan for said first database query;

wherein said first database query references one or more query-referenced database objects;

determining a second status of a first query-referenced database object of said one or more query-referenced database objects;

wherein, in said determining said second status, if said first query-referenced database object is the same as said first cache-query database object, then setting said second status to said first status;

wherein in said determining said second status step, if said first status is cold-status, then setting said second status to warm-status if there is a prior query optimization associated with an access plan for accessing said first query-referenced database object;

wherein said prior query optimization is one of a recently completed query optimization or an ongoing query optimization;

in said cost-based optimizing step, determining a cost of access to said first query-referenced database object based on said second status;

in said cost-based optimizing step, if a second cache query of said one or more cached queries is the same as said first database query, then validating a second access plan associated with said second cached query, and if said second access plan is validated, then reusing said second access plan for said first access plan;

wherein said validating said second access plan fails, if a cache status of any database object referenced by said second cached query is changed since said second cached query was executed; and

determining whether to remove said second access plan from said plan cache, if said validating said second access plan fails.