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 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

這條語句在其他大區的DB上執行均OK，在體驗服DB上執行報錯。從語法上看，確實是完全沒問題的。

碰到這種ORA-報錯的情況，我們可以透過設定ErrorStack對錯誤堆疊進行跟蹤，將錯誤的後臺資訊比較詳盡的轉儲到跟蹤檔案，供分析研究。

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 

alert日誌中的內容：

對應的跟蹤檔案是： /u/ora11g/diag/rdbms/xxxtest/xxxtest/trace/xxxtest_ora_29652.trc

我們來檢視跟蹤檔案中具體的資訊：

在跟蹤檔案中，我們看到針對我們發出的SQL，在Oracle底層已經將一些資料替換成了繫結變數，這個是因為我們設定了cursor_sharing=FORCE的。

那麼對於這個SQL，是否是因為cursor_sharing這個設定導致group by報錯了呢？

查詢了metalink，Cursor_sharing確實有對應的Bug會導致這個報錯：

文件上宣稱是11.2.0.2中是修復了此Bug的。但我們的環境就是11.2.0.2的哦。搞毛？

透過Oracle的堆疊資訊確認了是bug 8913729。

類似的堆疊資訊如下：

解決方法：

以下幾種都可以解決：

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如下：

已證實可行。