Sybase, Inc.

Edwin Anthony Seputis

Mihnea Andrei

Xun Cheng

Xiao Ming Zhou

Ramanujam Seshadri Halasipuram

Prateem Mandal

Sau Boon Lim

Disclosed herein are system, method, and computer program product embodiments for generating a histogram used to optimize a query plan. An embodiment operates by initializing a first thread and a second thread, such that the first thread processes a first section of a column and the second thread processes a second section of the column, concurrently with the first thread. The first thread generates a first hash table and the second thread generates a second hash table. The first and second hash tables represent data distribution stored in the respective first and second sections of the column. The first and second tables are merged into a histogram that represents data distribution in the column.

Online hash based optimizer statistics gathering in a database

System and methodology for automatic tuning of database query optimizer is described. In one embodiment, in a database system having an optimizer for selecting a query plan for executing a database query, a method of the present invention is described for automatically tuning query performance to prevent query performance regression that may occur during upgrade of the database system from a prior version to a new version, the method comprises steps of: in response to receiving a given database query for execution, specifying a query plan generated by the prior version's optimizer as a baseline best plan for executing the given database query; generating at least one new query plan using the new version's optimizer; learning performance for each new query plan generated by recording corresponding query execution metrics; if a given new query plan is observed to have better performance than the best plan previously specified, specifying that given new query plan to be the best plan for executing the given database query; if a given new query plan is observed to have worse performance than the best plan previously specified, specifying that given new query plan to be a bad plan to be avoided in the future; and automatically tuning future execution of the given database query by using the query plan that the system learned was the best plan.

System and methodology for automatic tuning of database query optimizer

System, method, computer program product embodiments and combinations and sub-combinations thereof for tuning query execution performance in a database management system are described. In an aspect, query optimization in a database management system (DBMS) with memory I/O (input/output) awareness includes determining compensated physical I/O (PIO) values indicative of memory I/O operations, including an approximate cache occupancy level, of an executing query in a DBMS, and utilizing the compensated PIO values as feedback to a query optimizer of the DBMS for optimization of future query execution.

Query optimization with memory I/O awareness

System, method, computer program product embodiments and combinations and sub-combinations thereof for tuning query execution performance in a database management system are described. In an aspect, query optimization in a database management system (DBMS) with awareness of limited resource usage includes identifying limited resource usage for each possible plan being considered for query execution by a query engine. A determination of whether any possible plan has limited resource usage that does not exceed a threshold level is made, and when at least one possible plan does not exceed the threshold level, the identified limited resource usage is included as a consideration during plan selection by the query engine. In an embodiment, temporary database usage is identified for each possible plan.

Query optimization with awareness of limited resource usage

System, method, computer program product embodiments and combinations and sub-combinations thereof for enhanced utilization of query optimization in a database system are provided. In an embodiment, query optimization of the database system is augmented to include statistics on temporary tables through an implicit update statistics option. Further, a statistics update is generated in the database system on a first optimizable query referencing at least one optimizable column of a temporary table when statistics for the at least one optimizable column are not already available.

Enhanced utilization of query optimization

Database system and methods are described for improving execution speed of database queries (e.g., for decision support). A multi-attribute selectivity optimization methodology is described that provides a more accurate estimate of the cost of a query execution plan, so that the predicted performance of the final execution plan will be more accurate. The densities by how much the selectivity deviates from a single attribute density and by how much the multi-attribute densities differ from one another are used as a basis for multi-selectivity estimates. The multi-attribute densities are used to scale estimates between extremes of total independence and total dependence. By taking into account how well attributes are correlated, the approach is able to provide more accurate multi-selectivity estimates. As a result, the database system can formulate better query plans and, thus, provide better performance.

Database system with methodology providing improved cost estimates for query strategies

Database system and methods are described for improving execution speed of database queries (e.g., for decision support) by provides methods employing spline histograms for improving the determination of selectivity estimates. The general approach improves histogram-based cost estimates as follows. The constant associated with a predicate (e.g., in r.a&gt;5, the constant is '5') is used to do a binary search in an array of histogram boundary values, for determining a particular histogram cell. Once a cell has been found, the system employs interpolation to find out how much of the cell has been selected. Once this interpolation value is found, it is used with a cell weighting and a spline value or weighting to estimate the selectivity of the predicate value, which takes into account how data values are distributed within the cell. As a result of increased accuracy of estimates, the system can formulate better query plans and, thus, provides better performance.

Growing community of inventors

Oakland, CA, United States of America

Edwin Anthony Seputis