Is it always the Optimizer? Should you disable Group By Elimination in 12c?

I wouldn’t say it’s always the optimizer – but sometimes one or two tiny little things are broken making it necessary to turn off new functionality for a while.

Please don’t misinterpret this posting!
As far as I see (and I really do work with customers!) I’d state the Oracle Database 12.1.0.2 Optimizer is more stable, faster and predictable compared to 11.2.0.x. Our Optimizer Architects and Developers have done a great job. But with all the complexity involved sometimes it takes a few fixes or incarnations until a great new feature really matures. The Group-By-Elimination feature in Oracle Database 12c seems to be such a candidate.

What does the feature do? A simple example demonstrates the feature.

First the elimination is OFF (FALSE):

SQL> explain plan for
  2  select /*+ opt_param('_optimizer_aggr_groupby_elim', 'false')*/
  3   dummy, sum(cnt)
  4    from (select dummy,
  5                 count(*) cnt
  6            from dualcopy
  7           group by dummy)
  8   group by dummy
  9  ; 

Explained
-----------------------------------------
|  Ld | Operation            | Name     |
-----------------------------------------
|   0 | SELECT STATEMENT     |          |
|   1 |  HASH GROUP BY       |          |
|   2 |   VIEW               |          |
|   3 |    HASH GROUP BY     |          |
|   4 |     TABLE ACCESS FULL| DUALCOPY |
-----------------------------------------

And now it’s ON (TRUE):

SQL> explain plan for
  2  select /*+ opt_param('_optimizer_aggr_groupby_elim', 'true')*/
  3   dummy, sum(cnt)
  4    from (select dummy,
  5                 count(*) cnt
  6            from dualcopy
  7           group by dummy)
  8   group by dummy
  9  ;

Explained 
---------------------------------------
| Ld  | Operation          | Name     |
---------------------------------------
|   0 | SELECT STATEMENT   |          |
|   1 |  HASH GROUP BY     |          |
|   2 |   TABLE ACCESS FULL| DUALCOPY |
---------------------------------------

By comparing the two execution plans you’ll see the difference immediately.

But there seem to be a few issues with that new feature such as several wrong query result bugs. The issues will be be tracked under the non-public bug20508819. Support may release a note as well.

At the moment we’d recommend to set:

_optimizer_aggr_groupby_elim=false

You’ll find this recommendation as well due to multiple wrong results bugs in:

  • MOS Note: 2034610.1
    Things to Consider to Avoid Poor Performance or Wrong Results on 12.1.0.2

for all PSUs and Bundle Patches for Oracle Database 12.1.0.2.

–Mike

3 thoughts on “Is it always the Optimizer? Should you disable Group By Elimination in 12c?

  1. Alex,

    please see MOS Note:19567916.8
    The fix for 19567916 is first included in 12.2.

    There’s also a single fix available for 12.1.0.2 on Linux64:

    WRONG RESULTS WHEN GROUP BY USED NESTED QUERIES IN 12.1.0.2(Patch 19567916)
    Linux x86-64 for Oracle 12.1.0.2.0

    But it doesn’t seem to be included in a PSU or BP yet. And please be aware that this may not be the only issue with that feature.

    Cheers
    Mike

Leave a Reply

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