_rowsets_enabled – Apply patch and use the default

I while back I blogged about issues with “rowsets“, a new Oracle 12c feature which unfortunately had two known wrong result (WQR) bugs:

What does “rowsets” actually mean?

I’d like to thank Sankar, our Development manager for providing this explanation which sheds some light on how important this feature actually is:

“Rowsets is a SQL execution performance enhancement introduced in Oracle RDBMS release 12.1 and further extended in a future release of the Oracle Database.

Prior to 12.1, data processing in the SQL layer were done on a row by row basis. That is, each row source in the row source tree performing a specific SQL operation (scan, join aggregation, window function, connect by etc) consume and produce one row at a time. Starting in 12.1, we introduced the concept of rowsets, where many row sources were enhanced to consume and produce rows in batches called rowsets. It is an internal performance
optimization project. That is why there isn’t much documentation.

This is a fundamental change in the SQL layer row processing. The benefits of rowsets is not only instruction cache locality but it also enables other performance optimizations such as, vectorization and prefetch in row sources and expression evaluation.”

Which issues did we see?

• bug 22173980 WRONG RESULTS WHEN “_ROWSETS_ENABLED” = TRUE
  • A hash join operation receives rowsets from its right input but then produces one row at a time as output resulting in a WQR
• bug 21608238  Wrong results with rowsets (enabled by default)
  • This issue got found with more than 255 columns in a table but there may be other possibilities to hit it

Which patches should you apply?

• Patches available for 22173980 WRONG RESULTS (NUMBER OF ROWS) WHEN “_ROWSETS_ENABLED” = TRUE
  • Included in PSU/BPs since April 2016
  • Several One-Off Patches available in addition for other releases
• Patches available for 21608238 WRONG RESULTS WITH ROWSETS
  • One-Off patch available on Linux for several Oracle 12.1.0.2 patch levels as well as on AIX on top of BP160419 only
  • You may have to request a one-off for your platform until the fix gets included into a Proactive BP

Summary

My recommendation would be to make sure that you are on the April/July 2016 Bundle Patch or PSU (or you take the one coming in October 2016) to cure the first bug mentioned above. The second issue (21608238) may need a one-off on top of your Proactive Bundle Patch or PSU so you may have to request it via an SR if it is not available on your platform.

Nevertheless the feature by itself is really important and shouldn’t be turned off when possible to benefit from the positive performance impact. It may be extended in the next release of the database.

–Mike