UPDATE: _rowsets_enabled in Oracle Database 12c

Please find a recent update here:

Last week I did post this entry with a strong recommendation to disable _rowsets_enabled in Oracle Database 12.1.0.2:

• Nov 10, 2015:
  Switch off _rowsets_enabled in Oracle Database 12c
  

Today I can give you an update, more insight information and better workarounds.

Credits go to our DWH and Optimizer people (thanks to Hermann, Angela, Nigel and Mohammed).

When is the problem happening?

When a hash join operation receives rowsets from its right input but then produces one row at a time as output. This explains why one of the bugs had as potential workaround hash_join_enabled=false (and please don’t use this as a w/a!!!).

Different Workarounds 

• Set
  event = “10055 trace name context forever, level 2097152”
  in your spfile. This will disable rowsets only for the specific situation where the problem happens.An important comment from Angela if you intend to set this event via an ALTER SESSION command:
  “Note that if the event is set with an alter session, it will not take effect unless the query or queries are recompiled, such as by changing the query text (by adding spaces or comments) or by flushing the shared pool.”
• Set
  _rowsets_enable=false
  in your spfile. But this will switch off the entire feature, not only the particular situation where the problem happens.
• Apply the fix for
  bug 22173980 WRONG RESULTS WHEN “_ROWSETS_ENABLED” =
  TRUE
  (as of Nov 16, 2015 in regression testing and not available yet) as soon as it is available.