Automatically determining optimization frequencies of queries with parameter markers

Inactive Publication Date: 2008-08-14
IBM CORP
View PDF18 Cites 21 Cited by
  • Summary
  • Abstract
  • Description
  • Claims
  • Application Information

AI Technical Summary

Benefits of technology

[0023]Advantageously, the present invention provides a technique for using selectivity or cardinality information to automatically determine the reoptimization mode of a query having parameter markers, thereby improving the productivity of DBAs and reducing the total cost of ownership of databases.

Problems solved by technology

This burdensome manual process of selecting a reoptimization mode decreases the productivity of the programmer or DBA and increases the total cost of ownership of the database system.
Further, a selection of a sub-optimal reoptimization mode slows down a program or the entire database system.

Method used

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
View more

Image

Smart Image Click on the blue labels to locate them in the text.
Viewing Examples
Smart Image
  • Automatically determining optimization frequencies of queries with parameter markers
  • Automatically determining optimization frequencies of queries with parameter markers
  • Automatically determining optimization frequencies of queries with parameter markers

Examples

Experimental program
Comparison scheme
Effect test

first embodiment

[0051]FIG. 2 is a flow diagram of a process of automatically determining optimization frequencies of queries with parameter markers in the system of FIG. 1, in accordance with embodiments of the present invention. The optimization frequency determination process of FIG. 2 begins at step 200. In step 202, a software tool (e.g., extract & transform tool 106 in FIG. 1) collects bind values of each parameter marker of a set of one or more parameter markers included in a query. In a first embodiment, the bind values are written into binary dump file 104 (see FIG. 1) (e.g., a RDSMon dump file provided by a DB2® feedback warehouse architecture) and the bind values are subsequently extracted from the binary dump file in step 202 by extract & transform tool 106 (see FIG. 1).

second embodiment

[0052]In a second embodiment, a software tool (not shown) that replaces extract & transform tool 106 in FIG. 1 uses single column statistics (e.g., information about frequent values and quantiles of a table's column) from system catalog tables to collect the bind values in step 202. Using single column database statistics in step 202 advantageously allows bind values to be collected according to their selectivities, thereby permitting the bind values to be used to analyze the whole query execution plan space, as described below relative to FIG. 4A. Hereinafter, a query execution plan space is also referred to simply as a plan space. Further, using database statistics in step 202 makes it possible to collect bind values for queries that never executed. On the other hand, using single column statistics in step 202 requires up-to-date statistics and refreshing such statistics is expensive. Further, collecting bind values from single column statistics assumes independence of the columns...

third embodiment

[0053]In a third embodiment, a software tool (not shown) that replaces extract & transform tool 106 in FIG. 1 randomly selects bind value sets in step 202. The bind value sets are selected from the tables and columns to which the parameter marker predicates of a query refer. Random selection of bind value sets in step 202 advantageously allows bind values for any query to be selected irrespective of whether the query ever executed. The number of collected bind values may be assigned as the values are randomly selected from the whole range of data. Further, using random sampling to collect bind values in step 202 allows whole bind value sets to be collected and not single bind values. Therefore, independence of the columns to which the parameter markers refer does not have to be assumed. However, it is very expensive to collect bind values by random sampling if the query joins large tables because the whole data range has to be scanned to ensure a satisfactory distribution of bind va...

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to View More

PUM

No PUM Login to View More

Abstract

A method for automatically determining optimization frequencies of queries having one or more parameter markers. Bind value sets and associated measurement sets are obtained. Ouerv execution plans and associated execution costs for optimal query execution with a bind value set are determined. Bind value set pairs for execution plans with maximum distance in selectivity or cardinality are determined. Execution costs for all pairs of plans with maximum selectivity / cardinality distance are determined. An optimization frequency is selected based on differences between the determined execution costs and optimal execution costs. If none of the differences exceeds a predefined value, the query is optimized once. If at least one of the differences exceeds the predefined value, the query is reoptimized each time the query is executed.

Description

FIELD OF THE INVENTION[0001]The present invention relates to a method and system for automatically determining optimization frequencies of queries with parameter markers.BACKGROUND OF THE INVENTION[0002]Conventionally, a programmer or a database administrator (DBA) managing a database system manually selects a reoptimization mode for queries having parameter markers. This selection of an optimal reoptimization mode depends not only on the query itself, but also on the bind values of the query's parameter marker(s). As these bind values can be unknown or change over time, an attempt to select an optimal reoptimization mode is a non-trivial procedure that may result in selecting a sub-optimal reoptimization mode. This burdensome manual process of selecting a reoptimization mode decreases the productivity of the programmer or DBA and increases the total cost of ownership of the database system. Further, a selection of a sub-optimal reoptimization mode slows down a program or the entire...

Claims

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to View More

Application Information

Patent Timeline
no application Login to View More
IPC IPC(8): G06F17/30
CPCG06F17/30463G06F17/30306G06F16/217G06F16/24542
InventorHUESKE, FABIANMARKL, VOLKER GERHARD
OwnerIBM CORP