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: |
|
|
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引起的,大家看到请帮我解答疑惑,谢谢