故障经过:
    客服电话告知有个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 <Bug:6471770>

结论: 
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,好了,问题处理了,先就这样吧.