Please find a recent update here:
Last week I did post this entry with a strong recommendation to disable _rowsets_enabled in Oracle Database 18.104.22.168:
- 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!!!).
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.”
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” =
(as of Nov 16, 2015 in regression testing and not available yet) as soon as it is available.
Update (Dec 2, 2015)
Thanks to Christian Ballweg from Optiz Consulting in Germany letting me know that the patch is available for download:
Thanks again to the Optimizer folks for their quick reaction!!!
Update (Sep 14, 2016)
Based on internal feedback let me clarify that a fix for the above issue is available widely, e.g. since the April 2016 PSU and the July 2016 BP but also as single fix on several platforms. Please follow the above link to access the information on MOS.
But furthermore let me also add that recently another “rowsets WQR” issue came up. Please see 22.214.171.124
Patch Set – Availability and Known Issues. It has a reference in the ALERTS section to:
|21608238+||Wrong results with rowsets (enabled by default)||
which seemed to be logged on ODA – and available fixes exist (as far as I interpret the MOS information correctly) on Linux only.