Insights into Statistics, Query Optimization and the Oracle Optimizer

New Oracle Optimizer White Paper

Nigel Bayliss
Product Manager

A new white paper for the Oracle Optimizer is now available. Just go to the Query Optimization page in OTN and click the Optimizer with Oracle Database 12c link.

I've kept the content and format close to the way Maria organized it, so it will be familiar to you if you've read previous versions. The main difference is that I've reduced the number of scripts in the text to keep the paper down to about 30 pages, otherise it's too easy to hit 40+. As an alternative, I upload examples to GitHub.

Post comments to this blog if you want to give me feedback on the white paper's content. If you want more examples, let me know and I'll add suggestions to my to-do list. Of course, I intend to post more entries here (and scripts to GitHub) to cover things in more detail anyway.

Finally, in OTN you'll see a bunch of other white papers referenced in the Optimizer page. At the time of writing, these are for Oracle Database 12c Release 1. Needless to say, updates to those are on the way.

Join the discussion

Comments ( 4 )
  • guest Tuesday, November 22, 2016

    Hi Nigel,

    Thanks for sharing this document demonstrating 12.2 new features.

    I have reviewed the whitepaper and I have not found any information about Band Joins in it: http://docs.oracle.com/database/122/TGSQL/joins.htm#GUID-24F34188-110F-4245-9DE7-43954092AFE0

    Perhaps you did not include them in your whitepaper in order to keep the number of pages below 30 like you said.

    I wondered whether you could provide additional examples with Band Joins either in the whitepaper or GitHub account.

  • NigelBayliss Tuesday, November 22, 2016

    Hi there,

    Yes, you're right, and it was intentional. I don't cover join types but I can definitely see why you might expect to see them in the WP. My immediate thought is to cover it here or in the DW blog. Let me think about it and I'll post something in this blog to steer you towards its ultimate destination.



  • Yury Pudovchenko Tuesday, December 6, 2016

    Hello, gurus,

    I migrated DB from 11.2 to 12.1. What the functionality is enabled with parameters: optimizer_features_enable=12.1 & optimizer_adaptive_features=FALSE ?

    What is the new features in 12.1 optimizer except adaptive features ?

    Thank you !

  • NigelBayliss Wednesday, December 7, 2016

    Hi Yury - using these settings you will be turning off the features covered in pages 2 to 10 (inclusive) of the white paper. I hope I have made it clear in the paper where a feature is new to Oracle Database 12c Release 2: you won't get those either of course.



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