Troubleshooting ORA-00979: not a GROUP BY expression after upgrade Oracle 12C

数据库版本升级都会强烈建议功能和性能测试，但有时还是不具备这样的条件或未测试全面，对于版本上线后的问题再见招拆招。最近遇到了一个11.2.0.3 升级 12.2 后有个存储过程无法执行，提示“ORA-00979: not a GROUP BY expression” 错误，显然是个SQL语法的问题。那很可能是哪个优化器新特性在查询转换过程中出现问题，下面还原这个问题, 和介绍一种简单粗暴的方法。

构建表结构，还原错误

create table anbob.test_t1(minbillcycle  varchar2(10));
insert into anbob.test_t1 select to_char(sysdate,'yyyymm') from dual;
create table anbob.test_t2 as select * from anbob.test_t1 where 1=0;

SQL> declare
      cycle#  number :=201911;
      aa number;
      begin
       SELECT 1 into aa
         FROM(
               select  months_between(to_date(CYCLE#,'yyyymm'),to_date(minbillcycle,'yyyymm')) DEBTTIME,count(distinct minbillcycle) DEBTSUBS
                 FROM   anbob.test_t1
                 GROUP BY months_between(to_date(CYCLE#,'yyyymm'),to_date(minbillcycle,'yyyymm'))
                union all
                SELECT 0 DEBTTIME,   0 DEBTSUBS 
                  FROM anbob.test_t2  )
      COMMIT;
      end;
  /
declare
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
ORA-06512: at line 5

Note:
特殊的地方这是一个变量聚合汇总子查询。

方法1
对上面的SQL，我们可以启用10053 event trace 分析查询转换后的SQL, 有一种测试方法就是session 级修改optimizer_features_enable的版本，其实经过测试发现降到10.2.0.5时，这个PL/SQL 就不在报错，判断应该是11G时引入的一个新特性(聚合参数转换)，很可能在原来的11G库时是禁用了某个优化器参数，而12c中没有禁用。

SQL> alter session set optimizer_features_enable='10.2.0.5';
Session altered.

SQL> declare
  2      cycle#  number :=201911;
  3      aa number;
  4      begin
  5       SELECT  1 into aa
  6         FROM(
  7               select  months_between(to_date(CYCLE#,'yyyymm'),to_date(minbillcycle,'yyyymm')) DEBTTIME,count(distinct minbillcycle) DEBTSUBS
  8                 FROM   system.test_t1
  9                 GROUP BY months_between(to_date(CYCLE#,'yyyymm'),to_date(minbillcycle,'yyyymm'))
 10                union all
 11                SELECT 0 DEBTTIME,   0 DEBTSUBS 
 12                  FROM system.test_t2  )
 13      COMMIT;
 14      end;
 15  /

PL/SQL procedure successfully completed.

方法2
修改optimizer_features_enable参数的影响范围太大，那缩小到最小的优化器特性是一种不错的选择，优化器相关的那么多参数，我们人工尝试太耗费时间，下面就写一个PLSQL 遍历所有的SESSION 级优化器参数，改变现在的值，暴力找出修改某参数可以解决当前的问题。

（基于x$qksceses、x$ksppi、x$ksppcv、x$kspvld_values、v$session_fix_control）
1, 生成测试脚本 sql_test.sql

-- file: params_test.sql
-- author: anbob.com

set serveroutput on

DECLARE
  l_unique_id              VARCHAR2(200);
  l_test_id                NUMBER := 0;
  l_test_id_rp_i           NUMBER := 0;
  l_spoolfile_name_p       VARCHAR2(100);
  l_spoolfile_name_vs      VARCHAR2(100); 
  l_spoolfile_name_rp_i_p  VARCHAR2(100);
  l_spoolfile_name_rp_i_vs VARCHAR2(100); 
  l_alter_session          VARCHAR2(4000);
  l_alter_session_bck      VARCHAR2(4000);
  l_skip_string_script     VARCHAR2(4000);
  l_skip_string_driver     VARCHAR2(4000);
  l_child_list             VARCHAR2(4000);
  PROCEDURE print (p_alter_session IN VARCHAR2)
  IS
  BEGIN
   l_test_id :=  l_test_id + 1;
   l_spoolfile_name_p := LPAD(l_test_id, 5, '0');
   dbms_output.put_line('PRO '||l_test_id||') "'||replace(p_alter_session,'ALTER SESSION SET',''));
   dbms_output.put_line(p_alter_session);
   dbms_output.put_line('@script');
  END;
begin
    FOR i IN (
         WITH cbo_param AS (
                SELECT /*+ materialize */ pname_qksceserow name
                  FROM x$qksceses
                 WHERE sid_qksceserow = SYS_CONTEXT('USERENV', 'SID')
                )
                SELECT x.indx+1 num,
                       x.ksppinm name,
                       x.ksppity type,
                       y.ksppstvl value,
                       y.ksppstdvl display_value,
                       y.ksppstdf isdefault,
                       x.ksppdesc description,
                       y.ksppstcmnt update_comment,
                       x.ksppihash hash
                  FROM x$ksppi x,
                       x$ksppcv y,
                       cbo_param
                 WHERE x.indx = y.indx
                   AND BITAND(x.ksppiflg, 268435456) = 0
                   AND TRANSLATE(x.ksppinm, '_', '#') NOT LIKE '##%'
                   AND x.ksppinm = cbo_param.name 
                   AND x.inst_id = USERENV('Instance')
                   AND DECODE(BITAND(x.ksppiflg/256, 1), 1, 'TRUE', 'FALSE') = 'TRUE'
                   AND x.ksppity IN (1, 2, 3)
				   --and lower(x.ksppinm) || ' ' || lower(x.ksppdesc) like lower('%parallel%')
                 ORDER BY x.ksppinm) 
LOOP
          IF SUBSTR(i.name , 1, 1) = CHR(95)  -- "_" 
		  THEN
            l_alter_session := 'ALTER SESSION SET "'||i.name ||'" = ';
          ELSE
            l_alter_session := 'ALTER SESSION SET '||i.name ||' = ';
          END IF;

          IF i.type = 1 THEN -- Boolean
            IF LOWER(i.value) = 'true' THEN
              l_alter_session := l_alter_session||' FALSE;';
            ELSIF LOWER(i.value) = 'false' THEN
              l_alter_session := l_alter_session||' TRUE;';
            ELSE
              dbms_output.put_line('--');
              dbms_output.put_line('-- skip test on '||i.name ||'. baseline value: '||i.value);
            END IF;
            print(l_alter_session);

          ELSIF i.type = 2 THEN -- String

            -- this is used as base ALTER SESSION for the LOV
            l_alter_session_bck := l_alter_session;

            FOR j IN (SELECT value_kspvld_values value
                        FROM x$kspvld_values
                       WHERE LOWER(name_kspvld_values) = i.name 

                         AND LOWER(value_kspvld_values) <> i.value
                       ORDER BY value_kspvld_values)
            LOOP
              l_alter_session := l_alter_session_bck||' '''||j.value||''';';
              print(l_alter_session);
            END LOOP;
		end if;			
end loop;

FOR i IN (SELECT * FROM v$session_fix_control WHERE session_id = SYS_CONTEXT('USERENV', 'SID') ORDER BY bugno) LOOP

          IF i.value = 0 THEN  --number
            l_alter_session := 'ALTER SESSION SET "_fix_control" = '''||i.bugno||':1'';';
          ELSIF i.value = 1 THEN
            l_alter_session := 'ALTER SESSION SET "_fix_control" = '''||i.bugno||':0'';';
          ELSE
            l_alter_session := 'ALTER SESSION SET "_fix_control" = '''||i.bugno||':0'';';
          END IF;
          print(l_alter_session);         
END LOOP;

end;
/

生成如下SQL:

PRO 1) " "_adaptive_window_consolidator_enabled" = FALSE;
ALTER SESSION SET "_adaptive_window_consolidator_enabled" = FALSE;
@script
PRO 2) " "_add_stale_mv_to_dependency_list" = FALSE;
ALTER SESSION SET "_add_stale_mv_to_dependency_list" = FALSE;
@script
PRO 3) " "_allow_level_without_connect_by" = TRUE;
ALTER SESSION SET "_allow_level_without_connect_by" = TRUE;
@script
PRO 4) " "_always_anti_join" = 'CHOOSE';
ALTER SESSION SET "_always_anti_join" = 'CHOOSE';
@script
PRO 5) " "_always_anti_join" = 'CUBE';
ALTER SESSION SET "_always_anti_join" = 'CUBE';
@script
...
...
...

2，把原PL/SQL 或测试的用户SQL 存放在当前目录的script.sql中

3，运行sql_test.sql

如果运行成功可以编辑sql_test.sql 把某参数删掉，继续尝试，最终我们发现修改以下三个参数任何一个都可以正常运行：

set “_optimizer_distinct_agg_transform”=false
set “_gby_hash_aggregation_enabled”=false
“_fix_control” = ‘11657903:1’

上面的两个隐藏参数还是有一些BUG的，但12.2中没有完全相似的，在11g是就是问题特性，我的建议是系统级禁用上面的两个参数，如

alter system set "_gby_hash_aggregation_enabled" = false SCOPE=SPFILE;

方法3，
SQL级禁用该参数。

SQL> declare
  2      cycle#  number :=201911;
  3      aa number;
  4      begin
  5       SELECT /*+opt_param('_optimizer_distinct_agg_transform','false')*/1 into aa
  6         FROM(
  7               select  months_between(to_date(CYCLE#,'yyyymm'),to_date(minbillcycle,'yyyymm')) DEBTTIME,count(distinct minbillcycle) DEBTSUBS
  8                 FROM   system.test_t1
  9                 GROUP BY months_between(to_date(CYCLE#,'yyyymm'),to_date(minbillcycle,'yyyymm'))
 10                union all
 11                SELECT 0 DEBTTIME,   0 DEBTSUBS 
 12                  FROM system.test_t2  )
 13      COMMIT;
 14      end;
 15  /

PL/SQL procedure successfully completed.

SQL>

Summary:
这里尝试了一种方法，当排查某个优化器参数时改变SQL的执行，遍历所有SESSION级优化器参数修改为有效的参数值，运行业务SQL, 同时还可以禁用或启用一些补丁修复的fix编号，同样的方法还可以应用于SQL执行性能的测试。