Is the Oracle Optimizer Getting Dumber ?

Is the Oracle Optimizer Getting Dumber ?

(NOTE: It’s likely that the majority of people don’t have these / any issues after performing upgrades. However, it’s still concerning to me).

A couple years ago I wrote an article on a performance hit frequently seen when upgrading to Oracle 12c.

This was due to a set of “adaptive” features Oracle included in the optimizer, seemingly to help performance. Yet, the result I (and apparently many others) had was a destruction of what was otherwise solid performance across the board.

I wish that was the last of my optimizer woes.

I recently underwent a Windows -> Linux migration, along with an upgrade from 10.2.0.5 -> 12.2.0.1. This was for a vendor application that had been performing well for many years but was long overdue for an upgrade.
During our testing phase of the migration, we hit a slew of performance issues. Certain specific queries that used to run sub-second were now taking 17 seconds. Again, stats did not help, at any estimate level. I disabled adaptive features. We still did not have acceptable performance and the migration was at risk due to this.

The next sensible move forward would be to look at the SQL plans in the current 10g database, and compare to what is occurring in theupgraded version. They did vary a bit. So, I used outline hints to create a sql profile for a couple of the problem SQL, and I got a portion of the app functioning well. Yet, the more that testing went on, the more areas we found we had issues with. It would be silly and a management nightmare to use SQL profiles on *every* single problem query. The Issue was much deeper than a single isolated SQL.

A last gut shot attempt to fix this: set optimizer_features_enable to 10.2.0.5.

I’m sure by now you can guess the result: the app performed exceptionally well and we had no more issues. A discussion with the vendor brought Out that they recommend using optimizer features at a level no higher than 12.1.0.1. We used that value and maintained excellent performance.

Yet, why should I have to do this ? Oracle is always trying to improve the optimizer and performance inside the RDBMS, but from what I have seen with each upgrade is not promising. Significant production issues have come out of some of these features. Any new optimizer improvements to me have been completely worthless in light of other glaring issues that have come up. This app is certified on versions of Oracle higher than what I can use for optimizer features.

I’m going to be moving production apps to 18c in the near future. I can’t wait to see what new optimizer nightmares – I mean features – I will face.

——————————————

(NOTE: the vendor came back and after some discussions with Oracle, they suspect this is possibly the root cause, though the symptoms do not match as I would expect them to):

High Version Counts For SQL Statements (>1024) Post Upgrade To 12.2 and Above Causing Database Slow Performance (Doc ID 2431353.1)

{{cta(‘6d2db1f2-3cb9-4426-bd66-b3a1c91322e1’)}}