We are doing a large ETL load from Oracle 12c. Every now and then Oracle optimizer decides to create a new execution plan for a SQL statement that has previously been running just fine. Usually this results in a 10x performance degradation. When I ask the client DBAs and Oracle consultants (client is a Gold partner), they usually ask us to convert ANSI joins to traditional Oracle joins. And usually this has helped. My question is, is the poor support for ANSI joins acknowledged by Oracle? Does Oracle recommend using traditional joins? (I've read Oracle books stating the exact opposite.)
This question has been answered by Chris Saxon of Oracle's "Ask TOM" team, who explains why ANSI joins are preferable to the old Oracle syntax:
This is because there are number of restrictions that apply to the (+) operator, but not the "outer join" clause. Follow the link for the full list.
The article does acknowledge that there can be cases where the old Oracle syntax is necessary (Materialized Views don't support ANSI - should be "fixed" in his view - implying it is a bug).
Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:
(rules excluded - follow link)
In your case, you might want to take a careful look at Plan Management (previously called Plan Stability - nice Oracle white paper here). The site (oracle-base) is a superb site for all things Oracle - I'd put it in my top 5 no matter what I was doing (dev, test, prod...).
The big plus is you won't get major swings in performance as a plan tilts from one optimization to the other - the price you pay for this is that you won't see performance improvements either! If you do implement plan management, then you should revisit those managed plans (on dev/test) from time to time to ensure that they are still optimal (or at least satisfactory).