bug 7716219 hash group by显示消耗大量的temp 表空间

SQL> set autotrace traceonly
SQL> select counter_account,
  2         counter_name,
  3         (transqty - BCCheck) as BCCheck,
  4         TSCheck,
  5         MMCheck,
  6         check_tsid,
  7         Check_Sellthroumanagerid,
  8         city_name_cn
  9    From (select /*+ index(t TRA_SUM_TRANSACTIONDATE_INDEX)*/
 10  mc.counter_account as counter_account,
 11                 mc.counter_name as counter_name,
 12                 sum(t.bccheckqty) as BCCheck,
 13                 sum(case
 14                       when ct.istschecked = 'N' or ct.istschecked is null then
 15                        1
 16                       else
 17                        0
 18                     end) as TSCheck,
 19                 sum(case
 20                       when ct.ismanagerchecked = 'N' or
 21                            ct.ismanagerchecked is null then
 22                        1
 23                       else
 24                        0
 25                     end) as MMCheck,
 26                 ts.user_id as check_tsid,
 27                 sum(t.transactionqty) as transqty,
 28                 mm.user_id as Check_Sellthroumanagerid,
 29                 mct.city_name_cn as city_name_cn
 30            from transaction_summary t
 31            left join css_tsmanagercheck ct on t.counterid = ct.counter_id
 32                                           and ct.check_month = '2010-11'
 33            left join CSS_COUNTER mc on mc.counter_id = t.counterid
 34            left join mst_city mct on mct.city_id = mc.city_id
 35            left join (select c.counter_id, u.user_id
 36                        from CSS_COUNTER      c,
 37                             sec_user_counter uc,
 38                             sec_user         u,
 39                             sec_role         r
 40                       where c.counter_id = uc.counter_id
 41                         and uc.user_id = u.user_id
 42                         and u.role_id = r.role_id
 43                         and r.role_id = '1B0BF35A76EE4247BC86DED761633001') mm on mm.counter_id 
=
 44                                                                                   t.counterid
 45            left join (select c.counter_id, u.user_id
 46                        from CSS_COUNTER      c,
 47                             sec_user_counter uc,
 48                             sec_user         u,
 49                             sec_role         r
 50                       where c.counter_id = uc.counter_id
 51                         and uc.user_id = u.user_id
 52                         and u.role_id = r.role_id
 53                         and r.role_id =
 54                             '95aa7015-c390-47af-8240-72a60aea667a') ts on ts.counter_id =
 55                                                                           t.counterid
 56           WHERE t.transactiondate >= '2010-11-01'
 57             AND t.transactiondate <= '2010-11-30' 
 58           group by t.counterid,
 59                    mc.counter_account,
 60                    mc.counter_name,
 61                    ts.user_id,
 62                    mm.user_id,
 63                    mct.city_name_cn) a
 64  
SQL>  
SQL> 
SQL>  /

----------------------------------------------------------
Plan hash value: 2441329485

----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                               |   200M|    82G|       |    25M  (1)| 84:54:35 |
|   1 |  HASH GROUP BY                    |                               |   200M|    82G|   170G|    25M  (1)| 84:54:35 |
|*  2 |   HASH JOIN RIGHT OUTER           |                               |   200M|    82G|       | 10999  (20)| 00:02:12 |
|   3 |    TABLE ACCESS FULL              | MST_CITY                      |  2686 | 53720 |       |     8   (0)| 00:00:01 |
|*  4 |    HASH JOIN RIGHT OUTER          |                               |    48M|    19G|       |  9367   (6)| 00:01:53 |
|   5 |     TABLE ACCESS FULL             | CSS_COUNTER                   |  5252 |   476K|       |    83   (0)| 00:00:02 |
|*  6 |     HASH JOIN RIGHT OUTER         |                               |    10M|  3388M|       |  8890   (2)| 00:01:47 |
|   7 |      VIEW                         |                               |  4805 |   614K|       |   187   (0)| 00:00:03 |
|   8 |       NESTED LOOPS                |                               |  4805 |  1440K|       |   187   (0)| 00:00:03 |
|   9 |        NESTED LOOPS               |                               |   335 | 90115 |       |   187   (0)| 00:00:03 |
|  10 |         NESTED LOOPS              |                               |    92 | 16284 |       |     3   (0)| 00:00:01 |
|* 11 |          INDEX UNIQUE SCAN        | PK2                           |     1 |    55 |       |     0   (0)| 00:00:01 |
|* 12 |          INDEX RANGE SCAN         | SEC_USER_ROLE_ID_IND          |    92 | 11224 |       |     3   (0)| 00:00:01 |
|* 13 |         INDEX RANGE SCAN          | PK_SEC_USER_COUNTER           |     4 |   368 |       |     2   (0)| 00:00:01 |
|* 14 |        INDEX UNIQUE SCAN          | PK_CSS_COUNTER                |    14 |   532 |       |     0   (0)| 00:00:01 |
|* 15 |      HASH JOIN RIGHT OUTER        |                               |  2635K|   495M|  5280K|  8615   (1)| 00:01:44 |
|  16 |       VIEW                        |                               | 37752 |  4829K|       |  1464   (1)| 00:00:18 |
|  17 |        NESTED LOOPS               |                               | 37752 |    11M|       |  1464   (1)| 00:00:18 |
|  18 |         NESTED LOOPS              |                               |  2629 |   690K|       |  1464   (1)| 00:00:18 |
|  19 |          NESTED LOOPS             |                               |   724 |   125K|       |    15   (0)| 00:00:01 |
|* 20 |           INDEX UNIQUE SCAN       | PK2                           |     1 |    55 |       |     0   (0)| 00:00:01 |
|* 21 |           INDEX RANGE SCAN        | SEC_USER_ROLE_ID_IND          |   724 | 88328 |       |    15   (0)| 00:00:01 |
|* 22 |          INDEX RANGE SCAN         | PK_SEC_USER_COUNTER           |     4 |   368 |       |     2   (0)| 00:00:01 |
|* 23 |         INDEX UNIQUE SCAN         | PK_CSS_COUNTER                |    14 |   532 |       |     0   (0)| 00:00:01 |
|* 24 |       HASH JOIN RIGHT OUTER       |                               | 81613 |  5260K|       |  6570   (1)| 00:01:19 |
|* 25 |        TABLE ACCESS FULL          | CSS_TSMANAGERCHECK            |  2662 | 79860 |       |    56   (2)| 00:00:
|  26 |        TABLE ACCESS BY INDEX ROWID| TRANSACTION_SUMMARY           | 81613 |  2869K|       |  6513   (1)
|* 27 |         INDEX RANGE SCAN          | TRA_SUM_TRANSACTIONDATE_INDEX | 81613 |       |       |   253   (1)| 00:00:04 
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("MC"."CITY_ID"=SYS_OP_C2C("MCT"."CITY_ID"(+)))
   4 - access("MC"."COUNTER_ID"(+)=SYS_OP_C2C("T"."COUNTERID"))
   6 - access("MM"."COUNTER_ID"(+)=SYS_OP_C2C("T"."COUNTERID"))
  11 - access("R"."ROLE_ID"=U'1B0BF35A76EE4247BC86DED761633001')
  12 - access("U"."ROLE_ID"=U'1B0BF35A76EE4247BC86DED761633001')
  13 - access("UC"."USER_ID"="U"."USER_ID")
  14 - access("C"."COUNTER_ID"="UC"."COUNTER_ID")
  15 - access("TS"."COUNTER_ID"(+)=SYS_OP_C2C("T"."COUNTERID"))
  20 - access("R"."ROLE_ID"=U'95aa7015-c390-47af-8240-72a60aea667a')
  21 - access("U"."ROLE_ID"=U'95aa7015-c390-47af-8240-72a60aea667a')
  22 - access("UC"."USER_ID"="U"."USER_ID")
  23 - access("C"."COUNTER_ID"="UC"."COUNTER_ID")
  24 - access("T"."COUNTERID"="CT"."COUNTER_ID"(+))
  25 - filter("CT"."CHECK_MONTH"(+)='2010-11')
  27 - access("T"."TRANSACTIONDATE">='2010-11-01' AND "T"."TRANSACTIONDATE"<='2010-11-30')

Note
-----
   - 'PLAN_TABLE' is old version


----------------------------------------------------------
        891  recursive calls
          0  db block gets
      22585  consistent gets
          0  physical reads
          0  redo size
     491828  bytes sent via SQL*Net to client
       3444  bytes received via SQL*Net from client
        193  SQL*Net roundtrips to/from client
         33  sorts (memory)
          0  sorts (disk)
       2880  rows processed

 

oracle 执行计划显示,hash group by消耗了170G的临时表空间,但是此条SQL显示在硬盘上的排序数据为0,不知道是什么原因,metalink 上检查发现一个Bug 7716219  HASH GROUP BY can use excessive TEMP space

具体描述如下:

This note gives a brief overview bug 7716219.
 The content was last updated on: 31-AUG-2010
 Click here for details of each of the sections below.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions BELOW 11.2
Versions confirmed as being affected	11.1.0.7 10.2.0.4
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

11.2.0.1 (Base Release) 11.1.0.7.1 (Patch Set Update) 10.2.0.5 (Server Patch Set)

 

Symptoms:	Related To:
Storage Space Usage Affected	Hash Join _GBY_HASH_AGGREGATION_ENABLED

Description

<!-- BEGIN BUGTAG DESCRIPTION -->

HASH GROUP BY can use excessive TEMP space.
 
Workaround
  Disable hash group by
  eg: Set "_gby_hash_aggregation_enabled" = false
 

<!-- END BUGTAG DESCRIPTION -->

 

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. Always consult with Oracle Support for advice.

 

References

Bug:7716219 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

我的数据库版本正好是10.2.0.4,执行计划统计出现问题不知道是不是由于这个 bug引起的,大家看到请帮我解答疑惑,谢谢