首页 » ORACLE, ORACLE [C]系列 » 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;规避问题。

 

打赏

对不起,这篇文章暂时关闭评论。