Automated Statistics Collection in DB2 UDB

Past work has suggested that query execution feedback can be useful in improving the quality of plans by correcting cardinality estimation errors in the query optimizer. The state-of-the-art approach for obtaining execution feedback is "passive" monitoring which records the cardinality of each operator in the execution plan. We observe that there are many cases where even after repeated executions of the same query with use of feedback from passive monitoring, suboptimal choices in the execution plan cannot be corrected. We present a novel "pay-as-you-go" framework in which a query potentially incurs a small overhead on each execution but obtains cardinality information that is not available with passive monitoring alone. Such a framework can significantly extend the reach of query execution feedback in obtaining better plans. We have implemented our techniques in Microsoft SQL Server, and our evaluation on real world and synthetic queries suggests that plan quality can improve significantly compared to passive monitoring even at low overheads.

show abstract

“…If the bit is set to 1, we increment the counter. 2 Note that the scan of a covering index is also sufficient.…”

Section: Mechanism For Join Expressionsmentioning

confidence: 99%

“…We note that the feedback obtained using proactive monitoring can also be used in the same scenarios where feedback from passive monitoring is used today e.g., for refining histograms [1][20], creating statistics [2], during query optimization for improving plans of other queries [21] and manual troubleshooting of plan quality.…”

Section: (A) (B)mentioning

confidence: 99%

A pay-as-you-go framework for query execution feedback

2008

View full text Add to dashboard Cite

show abstract

“…The data in the QFW is organized into relational tables. A detailed description can be found in [AHLL04]. …”

Section: The Query Feedback Warehousementioning

confidence: 99%

A learning optimizer for a federated database management system

Ewen¹,

Ortega-Binderberger

Markl

2005

Informatik Forsch. Entw.

View full text Add to dashboard Cite

Optimizers in modern DBMSs utilize a cost model to choose an efficient query execution plan (QEP) among all possible ones for a given query. The accuracy of the cost estimates depends heavily on accurate statistics about the underlying data. Outdated statistics or wrong assumptions in the underlying statistical model frequently lead to suboptimal selection of QEPs and thus to bad query performance. Federated systems require additional statistics on remote data to be kept on the federated DBMS in order to choose the most efficient execution plan when joining data from different datasources. Wrong statistics within a federated DBMS can cause not only suboptimal data access strategies but also unbalanced workload distribution as well as unnecessarily high network traffic and communication overhead.The maintenance of statistics in a federated DBMS is troublesome due to the independence of the remote DBMSs that might not expose their statistics or use different models and not collect all statistics needed by the federated DBMS.We present an approach that extends DB2s learning optimizer to automatically find flaws in statistics on remote data by extending its query feedback loop towards the federated architecture. We will discuss several approaches to get feedback from remote queries and present our solution that utilizes local query feedback and remote query feedback, and can also trigger and drive iterative sampling of remote data sources to retrieve information needed to compute statistics profiles. We provide a detailed performance study and analysis of our approach, and demonstrate in a case study a potential query execution speedup in orders of magnitude while only incurring a moderate overhead during query execution.

show abstract

“…In Section 2, we describe the exact cardinality query optimization problem and outline the architecture of our solution. Sections 3 and 4 1 Note that we only need to execute a COUNT(*) query corresponding to each expression respectively present the key technical ideas of Covering Queries optimization and generation of CASE queries for a workload to speed up exact cardinality query optimization. Section 5 presents results of our experimental evaluation and we discuss related work in Section 6.…”

Section: Example 1 Consider the Following Query On The Tpc-h Databasementioning

confidence: 99%

“…In this paper, we leverage execution feedback for the Covering Queries optimization (Section 3). There has been work related to collecting statistics (e.g., [1][10]) for improving the performance of ad-hoc queries. The focus of this work is not on ad-hoc queries but to address scenarios such as query optimizer testing where it is necessary to obtain the cardinality-optimal plan.…”

Section: Related Workmentioning

confidence: 99%

Exact cardinality query optimization for optimizer testing

Chaudhuri¹,

Narasayya²,

Ramamurthy³

2009

Proc. VLDB Endow.

View full text Add to dashboard Cite

The accuracy of cardinality estimates is crucial for obtaining a good query execution plan. Today"s optimizers make several simplifying assumptions during cardinality estimation that can lead to large errors and hence poor plans. In a scenario such as query optimizer testing it is very desirable to obtain the "best" plan, i.e., the plan produced when the cardinality of each relevant expression is exact. Such a plan serves as a baseline against which plans produced by using the existing cardinality estimation module in the query optimizer can be compared. However, obtaining all exact cardinalities by executing appropriate subexpressions can be prohibitively expensive. In this paper, we present a set of techniques that makes exact cardinality query optimization a viable option for a significantly larger set of queries than previously possible. We have implemented this functionality in Microsoft SQL Server and we present results using the TPC-H benchmark queries that demonstrate their effectiveness.

show abstract

Automated Statistics Collection in DB2 UDB

Cited by 29 publications

References 3 publications

A pay-as-you-go framework for query execution feedback

A pay-as-you-go framework for query execution feedback

A learning optimizer for a federated database management system

Exact cardinality query optimization for optimizer testing

Contact Info

Product

Resources

About