Optimizer Technical Papers
By Allison on Oct 20, 2011
We recently got a comment about our paper on adaptive cursor sharing and cardinality feedback, in which the author wished for more technical papers from the optimizer team. We've actually published papers on a few different topics over the years, so we wanted to summarize and post links to them here. Unfortunately they are not all available online for free, but where possible, we've posted links to the full text.
Cost-Based Query Transformation in Oracle
Rafi Ahmed, Allison W. Lee, Andrew Witkowski, Dinesh Das, Hong Su, Mohamed Zait, Thierry Cruanes: Cost-Based Query Transformation in Oracle. VLDB 2006: 1026-1036
This paper talks about many of the query transformations in Oracle 11g which we have discussed here before, including join elimination, view merging, subquery unnesting, join predicate pushdown, join factorization, plus some others that we have not covered here before. It also describes the cost-based framework itself, some of the challenges of transforming queries in a cost-based manner, and presents performance results for some experiments on a query workload from the Oracle E-Business Suite.
Optimizer Plan Change Management (full text)
Mohamed Ziauddin, Dinesh Das, Hong Su, Yali Zhu, Khaled Yagoub: Optimizer plan change management: improved stability and performance in Oracle 11g. PVLDB 1(2): 1346-1355 (2008)
This describes SQL plan management, which we discussed on here in a four-part SPM series. The paper gives a bit more detail about the internals of SPM, plus analyzes the behavior of SPM on a large query workload from Oracle E-Business Suite.
Closing the Query Processing Loop in Oracle 11g (full text)
Allison W. Lee, Mohamed Zait: Closing the query processing loop in Oracle 11g. PVLDB 1(2): 1368-1378 (2008)
This paper discusses adaptive cursor sharing and cardinality feedback, plus the general concept of using feedback from execution to improve plan selection. We've covered both of these topics on here before, but the paper also includes an analysis of the performance impact of the two features on a query workload from Oracle E-Business Suite.
Efficient and scalable statistics gathering for large databases in Oracle 11g
Sunil Chakkappen, Thierry Cruanes, Benot Dageville, Linan Jiang, Uri Shaft, Hong Su, Mohamed Zait: Efficient and scalable statistics gathering for large databases in Oracle 11g. SIGMOD Conference 2008: 1053-1064
This paper discusses two major improvements to statistics gathering in 11g -- approximate NDV and incremental statistics gathering. We've discussed both of these topics here before, but the paper has some additional details of the algorithms, plus results of experiments on a few different data sets.
Enhanced Subquery Optimizations in Oracle, VLDB 2009 (full text)
Srikanth Bellamkonda, Rafi Ahmed, Andrew Witkowski, Angela Amor, Mohamed Zait, Chun Chieh Lin: Enhanced Subquery Optimizations in Oracle. PVLDB 2(2): 1366-1377 (2009)
This paper talks about several of the newer subquery optimizations in Oracle. We talked about basic subquery unnesting in two posts here a while ago, and alluded to some of the other subquery transformations we perform -- this paper talks about all of those. It also talks about a new form of join elimination which we introduced in 11gR2. Nearly all of the topics in this paper are NOT covered here on the blog yet, but we hope to fix that soon!
For future reference, we will be adding a new section on the right side of the page called "Optimizer Technical Papers" with links to all of these papers.