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.
Update (Dec 2, 2015)
Thanks to Christian Ballweg from Optiz Consulting in Germany letting me know that the patch is available for download:
Bugs Resolved by This Patch
22173980 WRONG RESULTS (NUMBER OF ROWS) WHEN “_ROWSETS_ENABLED” = TRUE
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 12.1.0.2
Patch Set – Availability and Known Issues. It has a reference in the ALERTS section to:
21608238+ | Wrong results with rowsets (enabled by default) | 29/Jun/2016 |
which seemed to be logged on ODA – and available fixes exist (as far as I interpret the MOS information correctly) on Linux only.
–Mike
Mike,
Have you lost a digit from the end of the LEVEL? Possibly 5, 4, or 3
Looks like it should be 2^21 plus one or two low bits.
I guessed wrong.
Just received info from another source that it should be a 2 at the end – just one specific bit to set
Please note that the suggested level number for event 10055 is 2097152, not just 209715. Be sure to specify the trailing digit 2.
Sincerely,
Ted
Oracle Support Services
Ted,
thanks – copy/paste mistake.
You are correct – it should have been 2097152.
I corrected it.
Thanks
Mike
Jonathan,
you were right – it is 2097152 (and not 209715 as I had it first). My fault, happened during either edit or copy/paste.
Cheers and sorry for the inconvenience
Mike
Should "_rownum_enable" be "_rowsets_enabled"?
I think, there is another bug with "_rowsets_enabled". We applied the latest patchset (Jul 2016) and still got wrong results. When we set "_rowsets_enabled"=FALSE the result was correct.
Thanks about the hint for another issue. I’ve heard the same rumors … 😉
Cheers
Mike