Happy new year everyone! Let’s start the year with a simple question: what adaptive feature settings does Oracle recommend for the optimizer in Oracle Database 12c? I’ve covered this a couple times in the past (links below) but I’ve tended to dive into the details and unintentionally buried the short answer.
Here’s the Short Answer
Oracle Database 12c Release 2
For the majority of systems: use the defaults. There is no need to specifically set optimizer adaptive feature parameters.
By default, adaptive statistics are disabled and adaptive plans are enabled. If you are in a position to test the benefits of adaptive statistics on your system, then it’s something to consider using of course.
Oracle Database 12c Release 1
If you have a production environment that’s not encountering performance issues related to the adaptive optimizer then it’s more than reasonable to keep things as they are. In general, if you want to change adaptive feature settings in a production environment (or enable adaptive feature patches included in a bundle patch), then you should test before implementation.
If you are currently testing (or about to test) an upgrade to Oracle Database 12c Release 1, then it’s a good idea to apply the latest bundle patch and specifically enable the adaptive features patches. See Mike Dietrich’s blog post on this topic.
Here’s the Long Answer
Not everyone likes a short answer; the real-world can be very complicated. To that end, I have blogged in more detail about upgrades here and there are some specifics on the adaptive feature changes in Oracle Database 12c Release 2 here. There’s also Mike’s blog post as I mentioned earlier.
It’s ironic that in this post, the short answer looks longer than the long answer. Oh well.