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
Thanks Mike. Is there a special patch planned for this too( outside of the underscore parameter ) ?
Regards,
Alex
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
Thanks Mike.
Regards,
Alex