ORA-00979: not a GROUP BY expression报错处理

环境:Oracle Database 11gR2(11.2.0.2) on Linux 

故障现象: 执行Group By语句报错ORA-00979

具体的SQL如下:

SQL> select  a.d1,a.EXIT_type,round(a.cnt1/b.cnt2*100,2) from 

  2  (select substr(LOGIN_DATE,1,8) d1,EXIT_type,count(*) cnt1 fromxxx_connect_log group by substr(LOGIN_DATE,1,8),EXIT_type) a,

  3  (select substr(LOGIN_DATE,1,8) d2 ,count(*) cnt2 from xxx_connect_log group by substr(LOGIN_DATE,1,8) ) b

  4  where A.d1=B.d2 order by a.d1,a.EXIT_type;

(select substr(LOGIN_DATE,1,8) d2 ,count(*) cnt2 from xxx_connect_log group by substr(LOGIN_DATE,1,8) ) b

                                                                   *

ERROR at line 3:

ORA-00979: not a GROUP BY expression

这条语句在其他大区的DB上执行均OK,在体验服DB上执行报错。从语法上看,确实是完全没问题的。

碰到这种ORA-报错的情况,我们可以通过设置ErrorStack对错误堆栈进行跟踪,将错误的后台信息比较详尽的转储到跟踪文件,供分析研究。

ErrorStack的四个级别及说明:

0 Errorstack only 

1 Errorstack and function call stack 

2 As level1 plus the process state 

3 As level2 plus the context area 

ErrorStack仅在特定的错误出现的时候才被触发。可以在实例或者会话级别进行设置。

下面我们进行979的ErrorStack跟踪:

SQL> alter system set events='979 trace name errorstack forever,level 3';

 

System altered.

 

SQL> select  a.d1,a.EXIT_type,round(a.cnt1/b.cnt2*100,2) from 

  2  (select substr(LOGIN_DATE,1,8) d1,EXIT_type,count(*) cnt1 from xxx_connect_log group by substr(LOGIN_DATE,1,8),EXIT_type) a,

  3  (select substr(LOGIN_DATE,1,8) d2 ,count(*) cnt2 from xxx_connect_log group by substr(LOGIN_DATE,1,8) ) b

  4  where A.d1=B.d2 order by a.d1,a.EXIT_type;

(select substr(LOGIN_DATE,1,8) d2 ,count(*) cnt2 from xxx_connect_log group by substr(LOGIN_DATE,1,8) ) b

                                                                   *

ERROR at line 3:

ORA-00979: not a GROUP BY expression 

SQL> alter system set events='979 trace name errorstack off';

 

System altered. 

alert日志中的内容:

Fri May 3113:19:20 2013

OS Pid:29652 executed alter system set events '979 trace name errorstack forever,level3'

Errors infile /u/ora11g/diag/rdbms/xxxtest/xxxtest/trace/xxxtest_ora_29652.trc:

ORA-00979:not a GROUP BY expression

Fri May 3113:19:27 2013

Dumpingdiagnostic data in directory=[cdmp_20130531131927], requested by (instance=1,sid=29652), summary=[abnormal process termination].

Fri May 3113:19:52 2013

OS Pid:29652 executed alter system set events '979 trace name errorstack off'

对应的跟踪文件是:/u/ora11g/diag/rdbms/xxxtest/xxxtest/trace/xxxtest_ora_29652.trc

我们来查看跟踪文件中具体的信息:

dbkedDefDump():Starting a non-incident diagnostic dump (flags=0x0, level=12, mask=0x0)

----- ErrorStack Dump -----

ORA-00979:not a GROUP BY expression

-----Current SQL Statement for this session (sql_id=d2ccw741whuh0) -----

select  a.d1,a.EXIT_type,round(a.cnt1/b.cnt2*:"SYS_B_0",:"SYS_B_1")from

(selectsubstr(LOGIN_DATE,:"SYS_B_2",:"SYS_B_3")d1,EXIT_type,count(*) cnt1 from xxx_connect_log group bysubstr(LOGIN_DATE,:"SYS_B_4

",:"SYS_B_5"),EXIT_type)a,

(selectsubstr(LOGIN_DATE,:"SYS_B_6",:"SYS_B_7") d2 ,count(*) cnt2from xxx_connect_log group bysubstr(LOGIN_DATE,:"SYS_B_8",:"SYS_B

_9") )b

whereA.d1=B.d2 order by a.d1,a.EXIT_type

----- CallStack Trace -----

calling           call    entry            argument values in hex    

location          type    point            (? means dubious value)     

---------------------------- -------------------- ----------------------------

skdstdst()+36      call    kgdsdst()         000000000 ? 000000000 ?

                                      7FFFA7A0B468 ? 000000001 ?

                                      000000001 ? 

                                      

...

在跟踪文件中,我们看到针对我们发出的SQL,在Oracle底层已经将一些数据替换成了绑定变量,这个是因为我们设置了cursor_sharing=FORCE的。

那么对于这个SQL,是否是因为cursor_sharing这个设置导致group by报错了呢?

查询了metalink,Cursor_sharing确实有对应的Bug会导致这个报错:

 

8913729

11.2.0.2, 12.1.0.0

ORA-979 with CURSOR_SHARING=SIMILAR or FORCE

文档上宣称是11.2.0.2中是修复了此Bug的。但我们的环境就是11.2.0.2的哦。搞毛?

通过Oracle的堆栈信息确认了是bug 8913729。

类似的堆栈信息如下:

kgesev<- kgesec0="" -="" qcuerroer=""qcuerroep="" erroep="" em=""> <-qecgoc="" -="" qecsel="" qecpqbcheck=""qecdrv="" em="">  <-kkqcttcalo="" -="" kkqctdrvit=""apadrv="" pitca="" em="">

解决方法:

以下几种都可以解决:

1:通过设置CURSOR_SHARING=EXACT;

2:在SQL语句中使用hint  /*+ CURSOR_SHARING_EXACT */

3:设置optimizer_features_enable参数为10.2.0.5或者11.1.0.7(现在的值是11.2.0.2)。

我们选择第2种方式来解决:

修改后的SQL如下:

select  /*+ CURSOR_SHARING_EXACT */a.d1,a.EXIT_type,round(a.cnt1/b.cnt2*100,2) from 

(selectsubstr(LOGIN_DATE,1,8) d1,EXIT_type,count(*) cnt1 from xxx_connect_log group bysubstr(LOGIN_DATE,1,8),EXIT_type) a,

(selectsubstr(LOGIN_DATE,1,8) d2 ,count(*) cnt2 from xxx_connect_log group bysubstr(LOGIN_DATE,1,8) ) b

whereA.d1=B.d2 order by a.d1,a.EXIT_type;

已证实可行。

注:在11.2.0.3.0的解决方法

 alter session set cursor_sharing=exact; 

The issue is caused by 
Bug 16778475 ORA-979 with cursor_sharing=force 

来自 <http://blog.itpub.net/29154652/viewspace-772504/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值