Lately I’m in the process of upgrading a few clients to 18c and 19c, so I expected to hit a few issues. In this post I’ll discuss a specific issue with the optimizer resulting in ORA-30481.
Description of the Problem
This specific case happened with a client that used 11.2.0.3, so the upgrade was long overdue. I upgrade a test environment and the QA realized that one of the dashboards is getting an error. Checking the application log revealed that the error was indeed coming from Oracle:
ORA-30481: GROUPING function only supported with GROUP BY CUBE or ROLLUP
I checked the query and it wasn’t so complex. Reading the error message I checked the query and indeed it had a call to the GROUPING function without ROLLUP or CUBE in the GROUP BY clause.
However, this has worked in the 11.2, so I had to verify that. I changed the OPTIMIZER_FEATURES_ENABLE parameter to 11.2.0.4 and the query was executed successfully. I changed the same parameter to 12.1.0.2 and it failed with the same error. So this is something that has changed between 11.2.0.4 and 12.1.0.2.
I’m not sure if it should work and there is a bug from 12.1 onward, or if it shouldn’t work and 11.2 and older had a bug that allowed it to work. But let’s build a test case and see.
A Test Case
The first test case to write a simple query with GROUPING but without ROLLUP and CUBE and see what happens:
SQL> select x,count(y), grouping(x)
2 from
3 (select 1 as x,2 as y,3 as z from dual
4 union all
5 select 4 as x,5 as y, 6 as z from dual)
6 group by x;
X COUNT(Y) GROUPING(X)
---------- ---------- -----------
1 1 0
4 1 0
Okay, so it does seem to work, so why do I get this error? Looking at the query again I saw that there is another level of inline view that includes aggregation as well:
SQL> select x,count(y), grouping(x)
2 from
3 (select x,count(y) y
4 from
5 (select 1 as x,2 as y,3 as z from dual
6 union all
7 select 4 as x,5 as y, 6 as z from dual)
8 group by x) a
9 group by x
(select 1 as x,2 as y,3 as z from dual
*
ERROR at line 5:
ORA-30481: GROUPING function only supported with GROUP BY CUBE or ROLLUP
Good, so I managed to reproduce it. Now I need to understand what caused it.
I tried enabling 10053 event to get the optimizer trace. In the trace I couldn’t see any error, the trace just stopped after going through some rewrite options.
So next I went to my standard approach described here.
A Solution
After comparing the parameter between 11.2.0.4 and 12.1.0.2 I it didn’t take me a long time to find the parameter that cause this. In the 10053 trace I saw a few aggregation elimination retries, and then I saw that the parameter _OPTIMIZER_AGGR_GROUPBY_ELIM has changed from FALSE to TRUE when I changed to 12.1.0.2 optimizer. So I tried setting it to FALSE:
SQL> select /*+ opt_param('_OPTIMIZER_AGGR_GROUPBY_ELIM','FALSE') */ x,count(y), grouping(x)
2 from
3 (select x,count(y) y
4 from
5 (select 1 as x,2 as y,3 as z from dual
6 union all
7 select 4 as x,5 as y, 6 as z from dual)
8 group by x) a
9 group by x
X COUNT(Y) GROUPING(X)
---------- ---------- -----------
1 1 0
4 1 0
Cool, so now it works. I have an open SR for this (the engineer pointed me to note 2404925.1 which talks about this error. The fix in the note is to change the query, but that doesn’t explain why this query works even in 19c without the aggregation in the inline view.
I’ll update the SR with the information about _OPTIMIZER_AGGR_GROUPBY_ELIM and update this post if I have any news.
Update
When the engineer in the SR wrote: “there is no cause for that error and it occurs from 19c” and suggested that I do set the parameter, I understood that I won’t get a reasonable response about this. Things not happen “without a cause in 19c”, and I wanted to know if this is a bug or something else, even though I had a workaround.
So I contacted Nigel Bayliss, the optimizer PM at Oracle. If you don’t know Nigel, he’s a great guy and always willing to assist (like many other PMs at Oracle). It didn’t take a long time to get a response from him.
Nigel says that this is probably not related to the 12c bug, this is a different scenario. So he opened a bug on this (unpublished bug 32264848). It’s in low priority so I don’t expect a solution soon, but it’s good to know that this is reported. Thanks Nigel!
Hi , I had a same problem after upgraded from 12c to 19c , one of my Dashboard application getting slow .. generally Dashboard query execution time not take more than 2 sec but now it took 20 to 30 sec after upgraded 19c ..then i have tune the query using by SQL tuning advisor .. it recommended to use the SQL profile for the particular Query, it is working as expected but if customer give any condition or applying filters and sorting to the same query then again getting slow and impacted others places also …finally i have changed
optimzer_features_enable to 11.2.0.3 , Application is working as expected after this changes without any issues , even performance is looks good . application query is very big one and raised to Oracle support , they said to use the optimzer_features_enable to 11.2.0.3 for the particular query but i set into DB .
My question is should i keep with 11.2.0.3 in 19c DB or do something without use this parameter to get performance ..Please suggest thanks .
Hi,
I won’t change the optimizer_features_enable for the entire database. This will cripple the optimizer and will prevent good things from happening. Behavior like this is usually coming from a specific feature that can be turned off by a specific parameter.
When I run into a scenario like this I use the method I described here: https://gotodba.com/2016/03/15/optimizer-changes-after-upgrades/
Hope this helps
Liron
Hi Liron,
Thanks for your replied . i did not face any performance issue last one week after changed to 11.2.0.3 …Here i have shared parameter file , please have a look into this what are the parameters not required and how to avoid performance issue so that i will change optimizer_features_enable to 19.1.0
oltpprod.__data_transfer_cache_size=0
oltpprod.__db_cache_size=2986344448
oltpprod.__inmemory_ext_roarea=0
oltpprod.__inmemory_ext_rwarea=0
oltpprod.__java_pool_size=67108864
oltpprod.__large_pool_size=33554432
oltpprod.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
oltpprod.__pga_aggregate_target=4127195136
oltpprod.__sga_target=7683964928
oltpprod.__shared_io_pool_size=134217728
oltpprod.__shared_pool_size=3858759680
oltpprod.__streams_pool_size=33554432
oltpprod.__unified_pga_pool_size=0
*._adaptive_window_consolidator_enabled=TRUE
*._aggregation_optimization_settings=0
*._always_anti_join=’CHOOSE’
*._always_semi_join=’CHOOSE’
*._and_pruning_enabled=TRUE
*._b_tree_bitmap_plans=TRUE
*._bloom_filter_enabled=TRUE
*._bloom_folding_enabled=TRUE
*._bloom_pruning_enabled=TRUE
*._bloom_serial_filter=’ON’
*._bug27355984_xt_preproc_timeout=240
*._bug29394014_allow_triggers_on_vpd_table=TRUE
*._complex_view_merging=TRUE
*._connect_by_use_union_all=’TRUE’
*._convert_set_to_join=TRUE
*._cost_equality_semi_join=TRUE
*._cpu_to_io=0
*._dimension_skip_null=TRUE
*._distinct_agg_optimization_gsets=’CHOOSE’
*._eliminate_common_subexpr=TRUE
*._enable_type_dep_selectivity=TRUE
*._fast_full_scan_enabled=TRUE
*._first_k_rows_dynamic_proration=TRUE
*._gby_hash_aggregation_enabled=TRUE
*._gby_vector_aggregation_enabled=TRUE
*._generalized_pruning_enabled=TRUE
*._globalindex_pnum_filter_enabled=TRUE
*._gs_anti_semi_join_allowed=TRUE
*._improved_outerjoin_card=TRUE
*._improved_row_length_enabled=TRUE
*._index_join_enabled=TRUE
*._kgl_large_heap_warning_threshold=99428800
*._ksb_restart_policy_times=’0′,’60’,’120′,’240′
*._left_nested_loops_random=TRUE
*._local_communication_costing_enabled=TRUE
*._minimal_stats_aggregation=TRUE
*._mmv_query_rewrite_enabled=TRUE
*._new_initial_join_orders=TRUE
*._new_sort_cost_estimate=TRUE
*._nlj_batching_enabled=1
*._optim_adjust_for_part_skews=TRUE
*._optim_enhance_nnull_detection=TRUE
*._optim_new_default_join_sel=TRUE
*._optim_peek_user_binds=TRUE
*._optimizer_adaptive_cursor_sharing=TRUE
*._optimizer_aggr_groupby_elim=TRUE
*._optimizer_ansi_join_lateral_enhance=TRUE
*._optimizer_ansi_rearchitecture=TRUE
*._optimizer_batch_table_access_by_rowid=TRUE
*._optimizer_better_inlist_costing=’ALL’
*._optimizer_cbqt_no_size_restriction=TRUE
*._optimizer_cluster_by_rowid_batched=TRUE
*._optimizer_cluster_by_rowid_control=129
*._optimizer_cluster_by_rowid=TRUE
*._optimizer_coalesce_subqueries=TRUE
*._optimizer_complex_pred_selectivity=TRUE
*._optimizer_compute_index_stats=TRUE
*._optimizer_connect_by_combine_sw=TRUE
*._optimizer_connect_by_cost_based=TRUE
*._optimizer_connect_by_elim_dups=TRUE
*._optimizer_correct_sq_selectivity=TRUE
*._optimizer_cost_based_transformation=’LINEAR’
*._optimizer_cost_hjsmj_multimatch=TRUE
*._optimizer_cost_model=’CHOOSE’
*._optimizer_cube_join_enabled=TRUE
*._optimizer_dim_subq_join_sel=TRUE
*._optimizer_distinct_agg_transform=TRUE
*._optimizer_distinct_elimination=TRUE
*._optimizer_distinct_placement=TRUE
*._optimizer_dsdir_usage_control=126
*._optimizer_eliminate_filtering_join=TRUE
*._optimizer_enable_density_improvements=TRUE
*._optimizer_enable_extended_stats=TRUE
*._optimizer_enable_table_lookup_by_nl=TRUE
*._optimizer_enhanced_filter_push=TRUE
*._optimizer_extend_jppd_view_types=TRUE
*._optimizer_extended_cursor_sharing=’UDO’
*._optimizer_extended_cursor_sharing_rel=’SIMPLE’
*._optimizer_extended_stats_usage_control=192
*._optimizer_false_filter_pred_pullup=TRUE
*._optimizer_fast_access_pred_analysis=TRUE
*._optimizer_fast_pred_transitivity=TRUE
*._optimizer_filter_pred_pullup=TRUE
*._optimizer_fkr_index_cost_bias=10
*._optimizer_full_outer_join_to_outer=TRUE
*._optimizer_gather_feedback=TRUE
*._optimizer_gather_stats_on_load=TRUE
*._optimizer_group_by_placement=TRUE
*._optimizer_hybrid_fpwj_enabled=TRUE
*._optimizer_improve_selectivity=TRUE
*._optimizer_inmemory_access_path=TRUE
*._optimizer_inmemory_autodop=TRUE
*._optimizer_inmemory_bloom_filter=TRUE
*._optimizer_inmemory_cluster_aware_dop=TRUE
*._optimizer_inmemory_gen_pushable_preds=TRUE
*._optimizer_inmemory_minmax_pruning=TRUE
*._optimizer_inmemory_table_expansion=TRUE
*._optimizer_interleave_jppd=TRUE
*._optimizer_join_elimination_enabled=TRUE
*._optimizer_join_factorization=TRUE
*._optimizer_join_order_control=3
*._optimizer_join_sel_sanity_check=TRUE
*._optimizer_max_permutations=2000
*._optimizer_mode_force=TRUE
*._optimizer_multi_level_push_pred=TRUE
*._optimizer_multi_table_outerjoin=TRUE
*._optimizer_native_full_outer_join=’FORCE’
*._optimizer_new_join_card_computation=TRUE
*._optimizer_nlj_hj_adaptive_join=TRUE
*._optimizer_null_accepting_semijoin=TRUE
*._optimizer_null_aware_antijoin=TRUE
*._optimizer_or_expansion=’DEPTH’
*._optimizer_order_by_elimination_enabled=TRUE
*._optimizer_outer_join_to_inner=TRUE
*._optimizer_outer_to_anti_enabled=TRUE
*._optimizer_partial_join_eval=TRUE
*._optimizer_proc_rate_level=’BASIC’
*._optimizer_push_down_distinct=0
*._optimizer_push_pred_cost_based=TRUE
*._optimizer_reduce_groupby_key=TRUE
*._optimizer_rownum_bind_default=10
*._optimizer_rownum_pred_based_fkr=TRUE
*._optimizer_skip_scan_enabled=TRUE
*._optimizer_sortmerge_join_inequality=TRUE
*._optimizer_squ_bottomup=TRUE
*._optimizer_star_tran_in_with_clause=TRUE
*._optimizer_strans_adaptive_pruning=TRUE
*._optimizer_system_stats_usage=TRUE
*._optimizer_table_expansion=TRUE
*._optimizer_transitivity_retain=TRUE
*._optimizer_try_st_before_jppd=TRUE
*._optimizer_unnest_corr_set_subq=TRUE
*._optimizer_unnest_disjunctive_subq=TRUE
*._optimizer_unnest_scalar_sq=TRUE
*._optimizer_use_cbqt_star_transformation=TRUE
*._optimizer_use_feedback=TRUE
*._optimizer_use_gtt_session_stats=TRUE
*._optimizer_use_histograms=TRUE
*._optimizer_vector_transformation=TRUE
*._or_expand_nvl_predicate=TRUE
*._ordered_nested_loop=TRUE
*._parallel_broadcast_enabled=TRUE
*._partition_view_enabled=TRUE
*._pivot_implementation_method=’CHOOSE’
*._pre_rewrite_push_pred=TRUE
*._pred_move_around=TRUE
*._push_join_predicate=TRUE
*._push_join_union_view=TRUE
*._push_join_union_view2=TRUE
*._px_adaptive_dist_method=’CHOOSE’
*._px_concurrent=TRUE
*._px_cpu_autodop_enabled=TRUE
*._px_external_table_default_stats=TRUE
*._px_filter_parallelized=TRUE
*._px_filter_skew_handling=TRUE
*._px_groupby_pushdown=’FORCE’
*._px_join_skew_handling=TRUE
*._px_minus_intersect=TRUE
*._px_object_sampling_enabled=TRUE
*._px_parallelize_expression=TRUE
*._px_partial_rollup_pushdown=’ADAPTIVE’
*._px_partition_scan_enabled=TRUE
*._px_pwg_enabled=TRUE
*._px_replication_enabled=TRUE
*._px_scalable_invdist=TRUE
*._px_single_server_enabled=TRUE
*._px_ual_serial_input=TRUE
*._px_wif_dfo_declumping=’CHOOSE’
*._px_wif_extend_distribution_keys=TRUE
*._query_rewrite_setopgrw_enable=TRUE
*._remove_aggr_subquery=TRUE
*._replace_virtual_columns=TRUE
*._report_capture_cycle_time=0
*._right_outer_hash_enable=TRUE
*._selfjoin_mv_duplicates=TRUE
*._sql_model_unfold_forloops=’RUN_TIME’
*._subquery_pruning_enabled=TRUE
*._subquery_pruning_mv_enabled=FALSE
*._table_scan_cost_plus_one=TRUE
*._union_rewrite_for_gs=’YES_GSET_MVS’
*._unnest_subquery=TRUE
*._use_column_stats_for_function=TRUE
*.audit_file_dest=’/u01/app/oracle/admin/oltpprod/adump’
*.audit_trail=’DB’
*.compatible=’19.0.0′
*.control_files=’/Oracle1/oltpprod/oradata/oltpprod/control01.ctl’,’/Oracle2/oltpprod/control/oltpprod/control02.ctl’
*.cursor_sharing=’EXACT’
*.db_block_size=8192
*.db_domain=”
*.db_name=’oltpprod’
*.db_unique_name=’oltpprod’
*.diagnostic_dest=’/u01/app/oracle’
*.disk_asynch_io=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oltpprodXDB)’
*.filesystemio_options=’NONE’
*.job_queue_processes=100
*.log_archive_dest_1=’LOCATION=/Oracle5/oltpprod/admin/Arch/arch1′
*.log_archive_format=’arch_oltpprod_%t_%s_%r.arc’
*.log_buffer=536870912
*.memory_max_target=12884901888
*.memory_target=11811160064
*.open_cursors=600
*.optimizer_dynamic_sampling=2
*.optimizer_mode=’ALL_ROWS’
*.pga_aggregate_limit=12884901888
*.pga_aggregate_target=0
*.plsql_warnings=’DISABLE:ALL’
*.processes=800
*.query_rewrite_enabled=’TRUE’
*.remote_login_passwordfile=’EXCLUSIVE’
*.result_cache_max_size=0
*.service_names=’oltpprod’
*.sga_target=0
*.shared_pool_reserved_size=524288000
*.skip_unusable_indexes=TRUE
*.sql92_security=FALSE
Hi Baskar,
I’m sorry, but this is not something I can do over a blog post comment.
Seems like you have many underscore parameter configured and I don’t know why.
I suggest that you try what I explain in the blog post I mentioned in the previous comment. Get the parameters for 19.1, and the ones for 11.2, and just try them one by one until you find the one that cause you the performance issue. After that you can try and understand what it does and also update Oracle support if you want more information.
Liron