{"status":"ok","message-type":"work","message-version":"1.0.0","message":{"indexed":{"date-parts":[[2026,9,15]],"date-time":"2026-09-15T01:04:51Z","timestamp":1789434291577,"version":"build-2803163510"},"reference-count":23,"publisher":"Association for Computing Machinery (ACM)","issue":"12","content-domain":{"domain":[],"crossmark-restriction":false},"short-container-title":["Proc. VLDB Endow."],"published-print":{"date-parts":[[2017,8]]},"abstract":"<jats:p>Database Management Systems (DBMS) continue to be the foundation of mission critical applications, both OLTP and Analytics. They provide a safe, reliable and efficient platform to store and retrieve data. SQL is the lingua franca of the database world. A database developer writes a SQL statement to specify data sources and express the desired result and the DBMS will figure out the most efficient way to implement it. The query optimizer is the component in a DBMS responsible for finding the best execution plan for a given SQL statement based on statistics, access structures, location, and format. At the center of a query optimizer is a cost model that consumes the above information and helps the optimizer make decisions related to query transformations, join order, join methods, access paths, and data movement.<\/jats:p>\n          <jats:p>The final execution plan produced by the query optimizer depends on the quality of information used by the cost model, as well as the sophistication of the cost model. In addition to statistics about the data, the cost model also relies on statistics generated internally for intermediate results, e.g. size of the output of a join operation. This paper presents the problems caused by incorrect statistics of intermediate results, survey the existing solutions and present our solution introduced in Oracle 12c. The solution includes validating the generated statistics using table data and via the automatic creation of auxiliary statistics structures. We limit the overhead of the additional work by confining their use to cases where it matters the most, caching the computed statistics, and using table samples. The statistics management is automated. We demonstrate the benefits of our approach based on experiments using two SQL workloads, a benchmark that uses data from the Internal Movie Data Base (IMDB) and a real customer workload.<\/jats:p>","DOI":"10.14778\/3137765.3137785","type":"journal-article","created":{"date-parts":[[2017,9,7]],"date-time":"2017-09-07T13:35:53Z","timestamp":1504791353000},"page":"1813-1824","source":"Crossref","is-referenced-by-count":14,"title":["Adaptive statistics in Oracle 12c"],"prefix":"10.14778","volume":"10","author":[{"given":"Sunil","family":"Chakkappen","sequence":"first","affiliation":[{"name":"Oracle Corp."}],"role":[{"vocabulary":"crossref","role":"author"}]},{"given":"Suratna","family":"Budalakoti","sequence":"additional","affiliation":[{"name":"Oracle Labs"}],"role":[{"vocabulary":"crossref","role":"author"}]},{"given":"Ramarajan","family":"Krishnamachari","sequence":"additional","affiliation":[{"name":"Oracle Corp."}],"role":[{"vocabulary":"crossref","role":"author"}]},{"given":"Satyanarayana R","family":"Valluri","sequence":"additional","affiliation":[{"name":"Oracle Corp."}],"role":[{"vocabulary":"crossref","role":"author"}]},{"given":"Alan","family":"Wood","sequence":"additional","affiliation":[{"name":"Oracle Labs"}],"role":[{"vocabulary":"crossref","role":"author"}]},{"given":"Mohamed","family":"Zait","sequence":"additional","affiliation":[{"name":"Oracle Corp."}],"role":[{"vocabulary":"crossref","role":"author"}]}],"member":"320","published-online":{"date-parts":[[2017,8]]},"reference":[{"key":"e_1_2_1_1_1","doi-asserted-by":"publisher","DOI":"10.5555\/1182635.1164215"},{"key":"e_1_2_1_2_1","first-page":"1098","volume-title":"Automatic SQL tuning in oracle 10g. In (e)Proceedings of the Thirtieth International Conference on Very Large Data Bases","author":"Dageville B.","year":"2004","unstructured":"B. Dageville , D. Das , K. Dias , K. Yagoub , M. Za\u00eft , and M. Ziauddin . Automatic SQL tuning in oracle 10g. In (e)Proceedings of the Thirtieth International Conference on Very Large Data Bases , Toronto, Canada , August 31 - September 3 2004 , pages 1098 -- 1109 , 2004. B. Dageville, D. Das, K. Dias, K. Yagoub, M. Za\u00eft, and M. Ziauddin. Automatic SQL tuning in oracle 10g. In (e)Proceedings of the Thirtieth International Conference on Very Large Data Bases, Toronto, Canada, August 31 - September 3 2004, pages 1098--1109, 2004."},{"key":"e_1_2_1_3_1","doi-asserted-by":"publisher","DOI":"10.14778\/1687553.1687556"},{"key":"e_1_2_1_4_1","doi-asserted-by":"publisher","DOI":"10.5555\/1315451.1315533"},{"key":"e_1_2_1_5_1","volume-title":"VLDB'99","author":"K\u00f6nig A. C.","year":"1999","unstructured":"A. C. K\u00f6nig and G. Weikum . Combining histograms and parametric curve fitting for feedback-driven query result-size estimation . In VLDB'99 , Proceedings of 25th International Conference on Very Large Data Bases , September 7-10, 1999 , Edinburgh, Scotland, UK, pages 423--434, 1999. A. C. K\u00f6nig and G. Weikum. Combining histograms and parametric curve fitting for feedback-driven query result-size estimation. In VLDB'99, Proceedings of 25th International Conference on Very Large Data Bases, September 7-10, 1999, Edinburgh, Scotland, UK, pages 423--434, 1999."},{"key":"e_1_2_1_6_1","doi-asserted-by":"publisher","DOI":"10.14778\/1454159.1454178"},{"key":"e_1_2_1_7_1","doi-asserted-by":"publisher","DOI":"10.14778\/2850583.2850594"},{"key":"e_1_2_1_8_1","doi-asserted-by":"publisher","DOI":"10.14778\/1687627.1687738"},{"key":"e_1_2_1_9_1","volume-title":"Statistics for Engineers and Scientists","author":"Navidi W.","year":"2006","unstructured":"W. Navidi . Statistics for Engineers and Scientists . McGraw-Hill , New York , 2006 . W. Navidi. Statistics for Engineers and Scientists. McGraw-Hill, New York, 2006."},{"key":"e_1_2_1_10_1","unstructured":"IBM Developer Works. Get the most out of DB2 optimizer: Leveraging statistical views to improve query execution performance. https:\/\/www.ibm.com\/developerworks\/data\/library\/techarticle\/dm-1305leverage.  IBM Developer Works. Get the most out of DB2 optimizer: Leveraging statistical views to improve query execution performance. https:\/\/www.ibm.com\/developerworks\/data\/library\/techarticle\/dm-1305leverage."},{"key":"e_1_2_1_11_1","unstructured":"IBM Developer Works. Understand column group statistics in db2. http:\/\/www.ibm.com\/developerworks\/data\/library\/techarticle\/dm-0612kapoor\/index.html.  IBM Developer Works. Understand column group statistics in db2. http:\/\/www.ibm.com\/developerworks\/data\/library\/techarticle\/dm-0612kapoor\/index.html."},{"key":"e_1_2_1_12_1","unstructured":"MSDN. Statistics SQL Server 2016. https:\/\/msdn.microsoft.com\/en-us\/library\/ms190397.aspx.  MSDN. Statistics SQL Server 2016. https:\/\/msdn.microsoft.com\/en-us\/library\/ms190397.aspx."},{"key":"e_1_2_1_13_1","unstructured":"Oracle Blog. Extended statistics. https:\/\/blogs.oracle.com\/optimizer\/entry\/extended_statistics.  Oracle Blog. Extended statistics. https:\/\/blogs.oracle.com\/optimizer\/entry\/extended_statistics."},{"key":"e_1_2_1_14_1","unstructured":"Oracle Blog. How do I know what extended statistics are needed for a given workload. https:\/\/blogs.oracle.com\/optimizer\/entry\/how_do_i_know_what_extended_statistics_are_needed_for_a_given_workload.  Oracle Blog. How do I know what extended statistics are needed for a given workload. https:\/\/blogs.oracle.com\/optimizer\/entry\/how_do_i_know_what_extended_statistics_are_needed_for_a_given_workload."},{"key":"e_1_2_1_15_1","unstructured":"Oracle Documentation. Overview of the automatic workload repository. http:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e41573\/autostat.htm#PFGRF02601.  Oracle Documentation. Overview of the automatic workload repository. http:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e41573\/autostat.htm#PFGRF02601."},{"key":"e_1_2_1_16_1","unstructured":"Penn State. Chi-square table. http:\/\/sites.stat.psu.edu\/~mga\/401\/tables\/Chi-square-table.pdf.  Penn State. Chi-square table. http:\/\/sites.stat.psu.edu\/~mga\/401\/tables\/Chi-square-table.pdf."},{"key":"e_1_2_1_17_1","unstructured":"Wikipedia. Boole's inequality. https:\/\/en.wikipedia.org\/wiki\/Boole's_inequality.  Wikipedia. Boole's inequality. https:\/\/en.wikipedia.org\/wiki\/Boole's_inequality."},{"key":"e_1_2_1_18_1","doi-asserted-by":"publisher","DOI":"10.1109\/ICDE.2006.84"},{"key":"e_1_2_1_19_1","volume-title":"VLDB 2001, Proceedings of 27th International Conference on Very Large Data Bases","author":"Stillger M.","year":"2001","unstructured":"M. Stillger , G. M. Lohman , V. Markl , and M. Kandil . LEO - DB2's learning optimizer . In VLDB 2001, Proceedings of 27th International Conference on Very Large Data Bases , September 11-14, 2001 , Roma, Italy, pages 19--28 , 2001. M. Stillger, G. M. Lohman, V. Markl, and M. Kandil. LEO - DB2's learning optimizer. In VLDB 2001, Proceedings of 27th International Conference on Very Large Data Bases, September 11-14, 2001, Roma, Italy, pages 19--28, 2001."},{"key":"e_1_2_1_20_1","doi-asserted-by":"publisher","DOI":"10.14778\/2824032.2824051"},{"key":"e_1_2_1_21_1","doi-asserted-by":"crossref","DOI":"10.1007\/978-1-4419-5525-8","volume-title":"Fundamentals of Modern Statistical Methods: Substantially Improving Power and Accuracy","author":"Wilcox R. R.","year":"2010","unstructured":"R. R. Wilcox . Fundamentals of Modern Statistical Methods: Substantially Improving Power and Accuracy . Springer , 2010 . R. R. Wilcox. Fundamentals of Modern Statistical Methods: Substantially Improving Power and Accuracy. Springer, 2010."},{"key":"e_1_2_1_22_1","doi-asserted-by":"publisher","DOI":"10.1145\/2882903.2882914"},{"key":"e_1_2_1_23_1","doi-asserted-by":"publisher","DOI":"10.14778\/1454159.1454175"}],"container-title":["Proceedings of the VLDB Endowment"],"original-title":[],"language":"en","link":[{"URL":"https:\/\/dl.acm.org\/doi\/pdf\/10.14778\/3137765.3137785","content-type":"unspecified","content-version":"vor","intended-application":"similarity-checking"}],"deposited":{"date-parts":[[2022,12,28]],"date-time":"2022-12-28T10:05:41Z","timestamp":1672221941000},"score":1,"resource":{"primary":{"URL":"https:\/\/dl.acm.org\/doi\/10.14778\/3137765.3137785"}},"subtitle":[],"short-title":[],"issued":{"date-parts":[[2017,8]]},"references-count":23,"journal-issue":{"issue":"12","published-print":{"date-parts":[[2017,8]]}},"alternative-id":["10.14778\/3137765.3137785"],"URL":"https:\/\/doi.org\/10.14778\/3137765.3137785","relation":{},"ISSN":["2150-8097"],"issn-type":[{"value":"2150-8097","type":"print"}],"subject":[],"published":{"date-parts":[[2017,8]]}}}