International Business Machines Corporation

Andrew Peter Passe

Shantan Kethireddy

Ulrich Thiemann

Wei Hu

Robert J Bestgen

Michael Steven Faunce

Brian Robert Muras

Adam D Braham

Robert Douglas Driesch, Jr

Michael W Cain

Lynnette Elaine Carston

Edward J Miller

Kevin James Kathmann

David Glenn Carlson

Robert Victor Downer

Methods, systems, and products are provided for code path tracking. Embodiments include identifying an instrumented trace point in software code to be path tracked; identifying a function executed at the instrumented trace point in the software code; identifying parameters for the function executed at the instrumented trace point; and recording a description of the function, the parameters, and the result of the execution of the function using the parameters.

Code path tracking

A system and method of creating an access plan for a database query is disclosed. The system and method include identifying a first portion of a column of a table of a database. The first portion of the column may be in a select statement of the database query. The system and method include estimating a first statistical value for the column. The estimating may occur by analyzing a second portion of the column. The system and method include generating the access plan to predict a characteristic of a set of results for the database query. In generating the access plan, the first statistical value for the column may be used.

Access plan for a database query

Embodiments of the invention provide techniques for optimizing database queries for energy efficiency. In general, a query optimizer is configured to compare energy requirements of query plans, and to select a query plan requiring minimal energy to execute. In one embodiment, the query optimizer may also compare time performance of the query plans, and may select a query plan by matching to a user preference for a relative priority between energy requirements and time performance.

Organizing databases for energy efficiency

In an embodiment, a recursive query is received that comprises a first select statement with a seed select statement and a second select statement with a recursive reference, wherein the recursive query further identifies at least two columns in at least one table, wherein the at least two columns have parent-child relationships represented by nodes in a graph, wherein the graph represents the organization of values in rows in the at least one table. A target recursion depth is calculated for the graph based on a fanout of the graph. In an embodiment, the target recursion depth is calculated by summing the fanout at each recursion depth of the graph multiplied by a number of nodes at each recursion depth of the graph. An estimated number of rows that the recursive query will return is estimated based on the target recursion depth.

Estimating rows returned by recursive queries using fanout

Generating database query plans

A method, apparatus and program product to optimize a database query. A query plan is generated for the database query where the query plan includes first and second subplans. Cached optimization data associated with the first subplan and generated during evaluation of another query plan for a previous query that uses the first subplan is retrieved. The first subplan is evaluated for resource cost using the retrieved optimization data. The second subplan is also evaluated for resource cost. The query plan is then evaluated for resource cost based upon the evaluations of resource cost for the first and second subplans. A query plan is selected based on evaluations of resource cost and the plan is executed to generate a result set.

Portable and iterative re-usable suboptimization of database queries

A method, apparatus and program product dynamically activate and/or deactivate buffers during execution of a database query. The dynamic deactivation and activation is based on the monitoring of information associated with the consumption of data by a plurality of threads during execution of an access plan for the database query. By doing so, the selection of a less optimal buffer position during optimization may be replaced with a more optimal buffer position during runtime, often resulting in improved query performance and parallelism.

Database query processing with dynamically activated buffers

Embodiments of the invention provide techniques for aggregating database queries for energy efficiency. In one embodiment, queries received by a DBMS are aggregated and staged according to hard-disk drives required for query execution. Each group of queries accessing a given drive may be dispatched for execution together. Further, the queries received by a DBMS may be matched to patterns of previously received queries. The matching patterns may be used to predict other queries which are likely to be received by the DBMS. The received queries may be staged to be dispatched with the predicted queries. By aggregating queries to be executed, access to each hard-disk drive may be optimized, thus reducing the overall energy consumption required for executing the queries.

Grouping predicted database queries

A method, apparatus and program product to optimize a database query. In a first system having a first dataspace, a query plan is generated for the database query. Remote statistics collection associated with the database query is initiated on a second system having a second dataspace. The remote statistics collection uses the second dataspace, which includes a current copy of at least a portion of the first dataspace on the first system. Database statistics are collected for the first system by analyzing the second dataspace and then communicating the collected database statistics to the first system for use in optimizing database queries executed by the first system.

Database query optimization utilizing remote statistics collection

Aggregating database queries

Techniques for optimizing database queries for energy efficiency. A query optimizer is configured to compare energy requirements of query plans, and to select a query plan requiring minimal energy to execute. The query optimizer may also compare time performance of the query plans, and may select a query plan by matching to a user preference for a relative priority between energy requirements and time performance.

Optimizing database queries

A plan for executing a query in a relational database is obtained. A query for accessing data in the relational database is received. The query specifies N tables in the relational database from which data is to be retrieved. A determination is made whether a syntax of the query matches a syntax of a plan in a plan cache for executing the query. Matches are identified between generic table formats of the N tables specified in the query to generic table formats of N tables specified in the plan responsive to the syntax of the query matching the syntax of a plan in the plan cache for executing the query. The plan for executing the query is obtained based on whether the syntax of the query matches the syntax of the plan and based on identified matches between the generic table formats of the N tables specified in the query to the generic table formats of the N tables specified in the plan.

Growing community of inventors

Rochester, MN, United States of America

Andrew Peter Passe