ORA-00600: internal error code, arguments: [32695]

Xuan_Baby發表於2012-04-10
Error

故障經過:
    客服電話告知有個SQL執行很久都沒完成,首先看下aler.log日誌,發現報如下錯誤,
   Tue Apr 10 15:55:58 2012
Errors in file /oracle/admin/sjjs/udump/sjjs_ora_46268626.trc:
ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done], [], [], [], [], [], []
Tue Apr 10 15:56:07 2012
Errors in file /oracle/admin/sjjs/udump/sjjs_ora_48889922.trc:
ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done], [], [], [], [], [], []
Tue Apr 10 16:00:29 2012
Thread 1 advanced to log sequence 190859 (LGWR switch)
  Current log# 9 seq# 190859 mem# 0: /edasjjs03/sjjs/redo09.log
Tue Apr 10 16:01:04 2012
Errors in file /oracle/admin/sjjs/udump/sjjs_ora_34996380.trc:
ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done], [], [], [], [], [], []
Tue Apr 10 16:03:27 2012
Errors in file /oracle/admin/sjjs/udump/sjjs_ora_50069646.trc:
ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done], [], [], [], [], [], []
處理經過:
檢視/oracle/admin/sjjs/udump/sjjs_ora_46268626.trc檔案,
Current SQL statement for this session:
insert into DM_791.TB_D_ACTIVE_INFO
     (
        DAY_ID,
        SERV_ID,
        DAY_VOICE_CNT,
        DAY_VOICE_DUR,
   --省略----
        DAY7_INET_DUR,
        DAY7_INET_FLUX,
        DAY7_INET_CHARGE,
        DAY15_INET_CNT,
        DAY15_INET_DUR,
        DAY15_INET_FLUX,
        DAY15_INET_CHARGE,
        MON_P2P_CHARGE)
        SELECT
        20120408,
        A.SERV_ID,
  --省略------
        NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('voice','OCSVOICE') AND A.DAY_ID = 20120408 THEN A.COUNT END),0) DAY_VOICE_CNT,--當天通話次數
        NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('voice','OCSVOICE') AND A.DAY_ID = 20120408 THEN CEIL(A.DURATION/60) END),0) DAY_VOICE_DUR,--當天通話時長分鐘
        NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('voice','OCSVOICE') AND A.DAY_ID = 20120408 THEN A.CHARGE END),0) DAY_VOICE_CHARGE,--當天通話費用
        --NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('FixLocal','FixTrunk') AND A.DAY_ID BETWEEN 20120325 AND 20120408THEN A.CHARGE end),0) DAY15_GH_CHARGE,--連續15天通話費用
        --NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('FixLocal','FixTrunk') AND A.DAY_ID BETWEEN 20120401 AND 20120408THEN A.CHARGE end),0) MON_GH_CHARGE--當月累計通話費用
FROM  DM_791.TB_MID_SERV_ITEM_DAY A
WHERE A.SOURCE_FLAG IN ('voice','OCSVOICE','p2p','OCSP2P','INet','stream','OCSDATA')
AND A.DAY_ID BETWEEN 20120325 AND 20120408
GROUP BY 20120408,A.SERV_ID;
原來是碰到Oracle的Bug,趕緊去MOS看下吧,這個補丁僅僅是針對10.2.0.3的,在10.2.0.4上面沒有相應的Patch可以打,好吧,那就用他的Workaround方法吧.

Description

ORA-600 [32695] [hash aggregation can't be done] can occur 
for a GROUP BY query if hash aggregation is chosen.
 
Workaround
 Disable Hash group-by by setting
  "_gby_hash_aggregation_enabled" to FALSE,
  or by using a NO_USE_HASH_AGGREGATION hint 
 
Note: 
  Also see <>


結論: 
1、原始執行計劃
explain plan for
insert into DM_791.TB_D_ACTIVE_INFO
     (
        DAY_ID,
        SERV_ID,
        DAY_VOICE_CNT,
        DAY_VOICE_DUR,
   --省略----
        DAY7_INET_DUR,
        DAY7_INET_FLUX,
        DAY7_INET_CHARGE,
        DAY15_INET_CNT,
        DAY15_INET_DUR,
        DAY15_INET_FLUX,
        DAY15_INET_CHARGE,
        MON_P2P_CHARGE)
        SELECT
        20120408,
        A.SERV_ID,
  --省略------
        NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('voice','OCSVOICE') AND A.DAY_ID = 20120408 THEN A.COUNT END),0) DAY_VOICE_CNT,--當天通話次數
        NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('voice','OCSVOICE') AND A.DAY_ID = 20120408 THEN CEIL(A.DURATION/60) END),0) DAY_VOICE_DUR,--當天通話時長分鐘
        NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('voice','OCSVOICE') AND A.DAY_ID = 20120408 THEN A.CHARGE END),0) DAY_VOICE_CHARGE,--當天通話費用
        --NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('FixLocal','FixTrunk') AND A.DAY_ID BETWEEN 20120325 AND 20120408THEN A.CHARGE end),0) DAY15_GH_CHARGE,--連續15天通話費用
        --NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('FixLocal','FixTrunk') AND A.DAY_ID BETWEEN 20120401 AND 20120408THEN A.CHARGE end),0) MON_GH_CHARGE--當月累計通話費用
FROM  DM_791.TB_MID_SERV_ITEM_DAY A
WHERE A.SOURCE_FLAG IN ('voice','OCSVOICE','p2p','OCSP2P','INet','stream','OCSDATA')
AND A.DAY_ID BETWEEN 20120325 AND 20120408
GROUP BY 20120408,A.SERV_ID;
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 99598780
--------------------------------------------------------------------------------
| Id  | Operation                | Name                 | Rows  | Bytes |TempSpc
--------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                      |   331K|    12M|
|   1 |  HASH GROUP BY           |                      |   331K|    12M|    42M
|   2 |   PARTITION LIST ITERATOR|                      |   408K|    14M|
|*  3 |    TABLE ACCESS FULL     | TB_MID_SERV_ITEM_DAY |   408K|    14M|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("A"."SOURCE_FLAG"='INet' OR "A"."SOURCE_FLAG"='OCSDATA' OR "A"."SO
              "A"."SOURCE_FLAG"='OCSVOICE' OR "A"."SOURCE_FLAG"='p2p' OR "A"."SO
              "A"."SOURCE_FLAG"='voice')
2、使用Hint NO_USE_HASH_AGGREGATION
explain plan for
insert into DM_791.TB_D_ACTIVE_INFO
     (
        DAY_ID,
        SERV_ID,
        DAY_VOICE_CNT,
        DAY_VOICE_DUR,
   --省略----
        DAY7_INET_DUR,
        DAY7_INET_FLUX,
        DAY7_INET_CHARGE,
        DAY15_INET_CNT,
        DAY15_INET_DUR,
        DAY15_INET_FLUX,
        DAY15_INET_CHARGE,
        MON_P2P_CHARGE)
        SELECT /*+ NO_USE_HASH_AGGREGATION  */
        20120408,
        A.SERV_ID,
  --省略------
        NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('voice','OCSVOICE') AND A.DAY_ID = 20120408 THEN A.COUNT END),0) DAY_VOICE_CNT,--當天通話次數
        NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('voice','OCSVOICE') AND A.DAY_ID = 20120408 THEN CEIL(A.DURATION/60) END),0) DAY_VOICE_DUR,--當天通話時長分鐘
        NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('voice','OCSVOICE') AND A.DAY_ID = 20120408 THEN A.CHARGE END),0) DAY_VOICE_CHARGE,--當天通話費用
        --NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('FixLocal','FixTrunk') AND A.DAY_ID BETWEEN 20120325 AND 20120408THEN A.CHARGE end),0) DAY15_GH_CHARGE,--連續15天通話費用
        --NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('FixLocal','FixTrunk') AND A.DAY_ID BETWEEN 20120401 AND 20120408THEN A.CHARGE end),0) MON_GH_CHARGE--當月累計通話費用
FROM  DM_791.TB_MID_SERV_ITEM_DAY A
WHERE A.SOURCE_FLAG IN ('voice','OCSVOICE','p2p','OCSP2P','INet','stream','OCSDATA')
AND A.DAY_ID BETWEEN 20120325 AND 20120408
GROUP BY 20120408,A.SERV_ID;
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 28214388
--------------------------------------------------------------------------------
| Id  | Operation                | Name                 | Rows  | Bytes |TempSpc
--------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                      |   331K|    12M|
|   1 |  SORT GROUP BY           |                      |   331K|    12M|    42M
|   2 |   PARTITION LIST ITERATOR|                      |   408K|    14M|
|*  3 |    TABLE ACCESS FULL     | TB_MID_SERV_ITEM_DAY |   408K|    14M|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("A"."SOURCE_FLAG"='INet' OR "A"."SOURCE_FLAG"='OCSDATA' OR "A"."SO
              "A"."SOURCE_FLAG"='OCSVOICE' OR "A"."SOURCE_FLAG"='p2p' OR "A"."SO
              "A"."SOURCE_FLAG"='voice')
3、使用"_gby_hash_aggregation_enabled"
explain plan for
insert into DM_791.TB_D_ACTIVE_INFO
     (
        DAY_ID,
        SERV_ID,
        DAY_VOICE_CNT,
        DAY_VOICE_DUR,
   --省略----
        DAY7_INET_DUR,
        DAY7_INET_FLUX,
        DAY7_INET_CHARGE,
        DAY15_INET_CNT,
        DAY15_INET_DUR,
        DAY15_INET_FLUX,
        DAY15_INET_CHARGE,
        MON_P2P_CHARGE)
        SELECT /*+ OPT_PARAM('_gby_hash_aggregation_enabled','false') */
        20120408,
        A.SERV_ID,
  --省略------
        NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('voice','OCSVOICE') AND A.DAY_ID = 20120408 THEN A.COUNT END),0) DAY_VOICE_CNT,--當天通話次數
        NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('voice','OCSVOICE') AND A.DAY_ID = 20120408 THEN CEIL(A.DURATION/60) END),0) DAY_VOICE_DUR,--當天通話時長分鐘
        NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('voice','OCSVOICE') AND A.DAY_ID = 20120408 THEN A.CHARGE END),0) DAY_VOICE_CHARGE,--當天通話費用
        --NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('FixLocal','FixTrunk') AND A.DAY_ID BETWEEN 20120325 AND 20120408THEN A.CHARGE end),0) DAY15_GH_CHARGE,--連續15天通話費用
        --NVL(SUM(CASE WHEN A.SOURCE_FLAG IN ('FixLocal','FixTrunk') AND A.DAY_ID BETWEEN 20120401 AND 20120408THEN A.CHARGE end),0) MON_GH_CHARGE--當月累計通話費用
FROM  DM_791.TB_MID_SERV_ITEM_DAY A
WHERE A.SOURCE_FLAG IN ('voice','OCSVOICE','p2p','OCSP2P','INet','stream','OCSDATA')
AND A.DAY_ID BETWEEN 20120325 AND 20120408
GROUP BY 20120408,A.SERV_ID;
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 28214388
--------------------------------------------------------------------------------
| Id  | Operation                | Name                 | Rows  | Bytes |TempSpc
--------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                      |   331K|    12M|
|   1 |  SORT GROUP BY           |                      |   331K|    12M|    42M
|   2 |   PARTITION LIST ITERATOR|                      |   408K|    14M|
|*  3 |    TABLE ACCESS FULL     | TB_MID_SERV_ITEM_DAY |   408K|    14M|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("A"."SOURCE_FLAG"='INet' OR "A"."SOURCE_FLAG"='OCSDATA' OR "A"."SO
              "A"."SOURCE_FLAG"='OCSVOICE' OR "A"."SOURCE_FLAG"='p2p' OR "A"."SO
              "A"."SOURCE_FLAG"='voice')
執行計劃變了,現在使用的是sort group by避開Bug,好了,問題處理了,先就這樣吧.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26634508/viewspace-720843/，如需轉載，請註明出處，否則將追究法律責任。

相關文章