首页 » Cloud, ORACLE 9i-23ai » Alert: Oracle 19c ORA-30481 or ORA-7445 [qecgoc2()] _optimizer_aggr_groupby_elim
Alert: Oracle 19c ORA-30481 or ORA-7445 [qecgoc2()] _optimizer_aggr_groupby_elim
oracle升级是一项大工程，性能、语法、函数变化都可能导致应用无法使用，十几年前在记的在升级11g时因SQL语法修正了一个order by相关的bug， 使语法更加严格导致应用报错，导致应用和DBA争执最后大老板来平息， 最近几年升级19c的较多，一定要做足升级前测试，并不是只有wm_concat函数没了（WM_CONCAT is gone in 12c. Use LISTAGG instead.），再手动创建一个那么easy, 前几年升级12c是发过一个案例《Troubleshooting ORA-00979: not a GROUP BY expression after upgrade Oracle 12C》 ，也是同一个SQL在升级后提示语法错误。 这里演示一个19c 的另一个语法问题，后面会陆续在微信公众号分享 《oracle19c 避雷系列》。
USERNAME INST_NAME HOST_NAME I# SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS CDB$ROOT-anbob19c oel7db1 1 96 596 19.0.0.0.0 20210802 4229 33 4228 000000007816DF40 0000000078CF7F48
select x,count(z), grouping(x)
from
(select x,count(y) z
from
(select 1 as x,2 as y,3 as z from dual
union all
select 4 as x,5 as y, 6 as z from dual)
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
SQL> show parameter feature
PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
_exadata_feature_on boolean TRUE
disable_pdb_feature big integer 0
optimizer_features_enable string 19.1.0
SQL>
SQL> alter session set optimizer_features_enable='11.2.0.4';
Session altered.
select x,count(z), grouping(x)
from
(select x,count(y) z
from
(select 1 as x,2 as y,3 as z from dual
union all
select 4 as x,5 as y, 6 as z from dual)
group by x) a
9 group by x;
X COUNT(Z) GROUPING(X)
---------- ---------- -----------
1 1 0
4 1 0
SQL> alter session set optimizer_features_enable='12.1.0.2';
Session altered.
select x,count(z), grouping(x)
from
(select x,count(y) z
from
(select 1 as x,2 as y,3 as z from dual
union all
select 4 as x,5 as y, 6 as z from dual)
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
Note：
这是使用了一个grouping 的SQL, 在11.2.0.4运行正常，但是在19c提示ora-30481, 不过也确实没有使用group by cube或rollup, 经过反复尝试发现在12.1就发生了改变。
对于11.2.0.4和12.1 的参数变化
SQL> @comp_ofe_params 11.2.0.4 12.1.0.2 Compare Optimizer_Features_Enable Parameter differences for values 11.2.0.4 and 12.1.0.2 PARAMETER '11.2.0.4' '12.1.0.2' DESCRIPTION ------------------------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------- _gby_vector_aggregation_enabled FALSE TRUE enable group-by and aggregation using vector scheme _optimizer_unnest_scalar_sq FALSE TRUE enables unnesting of of scalar subquery _optimizer_cluster_by_rowid_control 3 129 internal control for cluster by rowid feature mode _optimizer_partial_join_eval FALSE TRUE partial join evaluation parameter _optimizer_ansi_rearchitecture FALSE TRUE re-architecture of ANSI left, right, and full outer joins _optimizer_use_gtt_session_stats FALSE TRUE use GTT session private statistics _optimizer_proc_rate_level OFF BASIC control the level of processing rates _distinct_agg_optimization_gsets OFF CHOOSE Use Distinct Aggregate Optimization for Grouping Sets _optimizer_inmemory_autodop FALSE TRUE optimizer autoDOP costing for in-memory _optimizer_nlj_hj_adaptive_join FALSE TRUE allow adaptive NL Hash joins _optimizer_vector_transformation FALSE TRUE perform vector transform optimizer_features_enable 11.2.0.4 12.1.0.2 optimizer plan compatibility parameter _optimizer_undo_cost_change 11.2.0.4 12.1.0.2 optimizer undo cost change _optimizer_ansi_join_lateral_enhance FALSE TRUE optimization of left/full ansi-joins and lateral views _optimizer_multi_table_outerjoin FALSE TRUE allows multiple tables on the left of outerjoin _optimizer_aggr_groupby_elim FALSE TRUE group-by and aggregation elimination _optimizer_cube_join_enabled FALSE TRUE enable cube join _optimizer_gather_stats_on_load FALSE TRUE enable/disable online statistics gathering _optimizer_batch_table_access_by_rowid FALSE TRUE enable table access by ROWID IO batching _optimizer_inmemory_table_expansion FALSE TRUE optimizer in-memory awareness for table expansion _optimizer_strans_adaptive_pruning FALSE TRUE allow adaptive pruning of star transformation bitmap trees _adaptive_window_consolidator_enabled FALSE TRUE enable/disable adaptive window consolidator PX plan _optimizer_cluster_by_rowid FALSE TRUE enable/disable the cluster by rowid feature _optimizer_reduce_groupby_key FALSE TRUE group-by key reduction _optimizer_inmemory_bloom_filter FALSE TRUE controls serial bloom filter for in-memory tables _optimizer_null_accepting_semijoin FALSE TRUE enables null-accepting semijoin _optimizer_hybrid_fpwj_enabled FALSE TRUE enable hybrid full partition-wise join when TRUE _optimizer_cluster_by_rowid_batched FALSE TRUE enable/disable the cluster by rowid batching feature _optimizer_inmemory_gen_pushable_preds FALSE TRUE optimizer generate pushable predicates for in-memory _optimizer_inmemory_access_path FALSE TRUE optimizer access path costing for in-memory _optimizer_inmemory_cluster_aware_dop FALSE TRUE Affinitize DOP for inmemory objects _optimizer_inmemory_minmax_pruning FALSE TRUE controls use of min/max pruning for costing in-memory tables 47 rows selected.
经过以上参数的回退操作
SQL> alter session set "_optimizer_aggr_groupby_elim"=false;
Session altered.
select x,count(z), grouping(x)
from
(select x,count(y) z
from
(select 1 as x,2 as y,3 as z from dual
union all
select 4 as x,5 as y, 6 as z from dual)
group by x) a
group by x;
X COUNT(Z) GROUPING(X)
---------- ---------- -----------
1 1 0
4 1 0
最终确认了修改set “_optimizer_aggr_groupby_elim”=false; 可以解决该问题。
当然如果修改语法也可以解决
select x,count(z), grouping(x)
from
(select x,count(y) z
from
(select 1 as x,2 as y,3 as z from dual
union all
select 4 as x,5 as y, 6 as z from dual)
group by x) a
9 group by CUBE(x);
X COUNT(Z) GROUPING(X)
---------- ---------- -----------
2 1
1 1 0
4 1 0
与_optimizer_aggr_groupby_elim参数相关在12c也有多个bug, 另外在19c中同样还可能遇到ORA-7445 [qecgoc2()] 错误,而不是ora-979语义错误，确认为已知Bug 30889723 影响19c多个版本。
qecgoc –> query execute edit checking group operand check
解决方法同样是Setting “_optimizer_aggr_groupby_elim”=false;
所以建议在19c中建议主动把 “_optimizer_aggr_groupby_elim”=false;规避问题。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。