Tuesday Nov 08, 2011
Thursday Oct 20, 2011
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.
Monday May 09, 2011
By Maria Colgan-Oracle on May 09, 2011
Monday Mar 28, 2011
By Maria Colgan-Oracle on Mar 28, 2011
We have gotten a lot of questions recently about what happened to the Optimizer white papers that use to be on OTN. The white papers are still there but the URL has changed slightly, which is causing the problem. Here are new URL's for the most recent Optimizer white papers:
Wednesday Feb 27, 2008
By Maria Colgan-Oracle on Feb 27, 2008
The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.
- How to Use SQL Plan Management
- Group-by and Aggregation Elimination
- Upgrade to Oracle Database 12c and Avoid Query Regression
- Oracle OpenWorld 2015
- Tips on SQL Plan Management and Oracle Database In-Memory – Part 3
- Tips on SQL Plan Management and Oracle Database In-Memory - Part 2
- Tips on SQL Plan Management and Oracle Database In-Memory Part 1
- What you need to know about SQL Plan Management and Auto Capture
- Space Management and Oracle Direct Path Load
- Controlling Access To The In-Memory Column Store Via Hints
Optimizer White papers
- Best Practices For Gathering Optimizer Statistics In Oracle Database 11g - Part2
- SQL Plan Management in Oracle Database 12c
- Understanding Optimizer Statistics in Oracle Database 12c
- Upgrading from 11g to 12c: What to expect from the Optimizer
- Best Practices For Gathering Optimizer Statistics In Oracle Database 12 - Part2
- SQL Plan Management in Oracle Database 11g
- Understanding Optimizer Statistics in Oracle Database 11g
- Upgrading from 10g to 11g: What to expect from the Optimizer
- Explain the Explain Plan
- Upgrading from 9i to 10g: What to expect from the Optimizer
Optimizer Development Technical Papers
- Closing the Query Processing Loop in Oracle 11g
- Cost-Based Query Transformation in Oracle
- Efficient and scalable statistics gathering for large databases in Oracle 11g
- Enhanced Subquery Optimizations in Oracle, VLDB 2009
- Optimizer plan change management: improved stability and performance in Oracle 11g
- /Cursor Sharing
- /Diagnostic tools
- /How do I
- /Oracle OpenWorld
- /Oracle Optimizer
- /SQL Plan Management
- /Upcoming events