Automated Statistics Collection in Action Peter J. Haas, Mokhtar Kandil, Alberto Lerner, Volker Markl, Ivan Popivanov, Vijayshankar Raman, and Daniel C. Zilio   View Paper (PDF)   Return to Group 3 Demonstrations Abstract If presented with inaccurate statistics, even the most sophis- ticated query optimizers make mistakes. They may wrongly estimate the output cardinality of a certain operation and thus make sub-optimal plan choices based on that cardi- nality. Maintaining accurate statistics is hard, both be- cause each table may need a specifically parameterized set of statistics and because statistics get outdated as the database changes. Automated Statistic Collection (ASC) is a new component in IBM DB2 UDB that, without any DBA inter- vention, observes and analyzes the effects of faulty statistics and, in response, it triggers actions that continuously re- pair the latter. In this demonstration, we will show how ASC works to alleviate the DBA from the task of maintain- ing fresh, accurate statistics in several challenging scenarios. ASC is able to reconfigure the statistics collection parame- ters (e.g, number of frequent values for a column, or corre- lations between certain column pairs) on a per-table basis. ASC can also detect and guard against outdated statistics caused by high updates/inserts/deletes rates in volatile, dy- namic databases. We will also show how ASC works from the inside: from how cardinality mis-estimations are intro- duced in different kind of operators, to how this error is propagated to later operations in the plan, to how this in- fluences plan choices inside the optimizer. ©2006 Association for Computing Machinery