A week passed by since I installed 10.2.0.5 + Patch 2 on a bunch of production servers. Time for a short confession.
Was it trouble free? Of course not. :)

One particular problem hit us pretty hard. At the time of this writing the case is not yet fully resolved nor documented with a repeatable test cases so I can’t give you any proof in the shape of code, I’m afraid you’ll have to believe on my word. :-)

While troubleshooting performance problem of some reports that run well on 10.2.0.3 (elapsed time measured in tens of seconds), but get crazy execution plans after patchset installation (elapsed time measured in hours, if not days!) .

The pattern that I spotted was:

• in all cases complex views are involved in the queries (outer joins, nested selects, unions…you named it),
• roughly half of the views were written using ANSI join syntax, the other half used old-style join syntax,
• performance problem was reported only on queries that selected data from the views having ANSI join syntax, old-style views performed well
• if the problematic view with ANSI join was rewritten to old-style, it performed well

Workarounds that we found:

• rewrite views containing ANSI joins to use old style join syntax,
• hint the views with /*+ NO_QUERY_TRANSFORMATION */ hint,
• disable CBO query transformations at the session level with logon trigger such as:

CREATE OR REPLACE TRIGGER disable_cbo_qt
 AFTER LOGON ON DATABASE
 WHEN (USER IN('APP1','APP2'))
 BEGIN
   -- turn CBO query transformation from LINEAR to OFF
   execute immediate 'alter session set "_optimizer_cost_based_transformation"=off';
 END;
 /

We’ll very likely replace ANSI joins in the views with old-style joins on the long run, at the moment hint was placed in the views. I don’t like the idea to completely turn off query transformation at the session level.

In theory there should be no difference from the performance point of the view between old-style joins vs. ANSI joins. In theory. If you’re using ANSI joins (and there is no reason to be discouraged of using them after reading this post, really!), then at least remember that sometimes is worthwhile trying to refactor poorly performing query having ANSI join with equivalent one written in old-style syntax. I hope Oracle will eliminate such “surprises” in the future.