Each time we release a new version of the Oracle Database we publish a white paper that covers what's in the new in the Oracle Optimizer. It's pretty comprehensive and it covers all of the headline features, but it doesn't go deep down in amongst the dragons.What if you want a closer look a what's changed in the Oracle Optimizer between releases? That's what this post is about.
You'll know that there's a database parameter called optimizer_features_enable (OFE). It can be used to restrict which Optimizer features are enabled in your database. You'll also know that you shouldn't use it like a magic wand, but it is very useful for the trick I have in mind here.
I've put together a script for you to check out. It's not earth-shatteringly new (as you'll realize if you take a look around the Internet) but I think that you'll find it useful. The principle behind its operation is pretty simple: it captures parameters and configuration settings for two different OFE values (220.127.116.11 and 18.104.22.168, by default). Finally, it displays a comparison between the two sets of results. I think that the output is very interesting. I hope you think so too.