I wrote before about optimizer changes after upgrades, so this is a quick post with some more information related to this topic.
As I wrote in the post I mentioned above, most of the issues we run into after upgrading are performance related. I guess this is the case because it’s one of the most complex components of Oracle and it’s very very difficult (if not impossible) to test everything.
Once a new version is out, people are starting to play with it, and after a while use it on test and then production systems. During this time, bugs are identified and fixed. With these issues Oracle behaves responsibly and provides a lot of information about problems (I even wrote in the past that I like to read notes talking about bug fixes).
With optimizer issues, they are doing the same, so MOS has specific notes about optimizer bugs and issues by version. The noted are titled: “Things to Consider to Avoid Poor Performance or Wrong Results on <version>”. Note 2034610.1 for example is for 18.104.22.168 and note 1645862.1 is for 22.214.171.124, but there is one for every version from 126.96.36.199 (currently to 188.8.131.52, but I guess they will have one for 12.2 soon).
My recommendation is that before you upgrade, take a look at this. That way you will know about well-known bugs in the new version, and will be able to avoid them before going live.