UPDATE: _rowsets_enabled in Oracle Database 12c

Please find a recent update here:

_rowsets_enabled – Apply patch and use the default


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

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

9 thoughts on “UPDATE: _rowsets_enabled in Oracle Database 12c

  1. 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

  2. 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

  3. 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.

  4. Pingback: Switch off “_rowsets_enabled” in Oracle Database 12c | Upgrade your Database - NOW!

Leave a Reply

Your email address will not be published. Required fields are marked *