Insights into Statistics, Query Optimization and the Oracle Optimizer

Optimizer Technical Papers

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.

Join the discussion

Comments ( 2 )
  • Vamshi Damidi Wednesday, October 26, 2011

    Hi Team,

    I am following this blog for quiet sometime and i believe if such precious documents are free to read would help us.


    Vamshi .D

  • lale Monday, April 15, 2013

    dissapointing that such important Oracle papers are not freely available ...

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Integrated Cloud Applications & Platform Services