1652
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
0
select GAME_CARD_TYPE.NAME,
GAME.NAME GameName,
sum(V_SALE_TMP_LOG.GAME_CARD_NUM) as num,
sum(V_SALE_TMP_LOG.CITY_AGENT_COST) as sumSalePRice,
sum(V_SALE_TMP_LOG.PROVINCE_AGENT_COST) as basePrice,
V_SALE_TMP_LOG.SALE_MODE
from V_SALE_TMP_LOG,
GAME_CARD_TYPE,
GAME,
RESELLER_BASE,
AGENT_BASE c,
AGENT_BASE d
where (V_SALE_TMP_LOG.SALE_MODE=2 or V_SALE_TMP_LOG.SALE_MODE=3 or V_SALE_TMP_LOG.SALE_MODE=4)
and V_SALE_TMP_LOG.GAME_CARD_TYPE_ID=GAME_CARD_TYPE.GAME_CARD_TYPE_ID(+)
and GAME_CARD_TYPE.GAME_ID=GAME.GAME_ID(+)
and V_SALE_TMP_LOG.RESELLER_ID=RESELLER_BASE.RESELLER_ID
and RESELLER_BASE.AGENT_ID=c.AGENT_ID
and c.PARENT_AGENT_ID = d.AGENT_ID
and V_SALE_TMP_LOG.IS_SUCCESS='Y'
and d.AGENT_ID=52080
and V_SALE_TMP_LOG.LOG_TIME>=to_date('2004-05-04 00:00:00','yyyy-mm-dd HH24:MI:SS') and V_SALE_TMP_LOG.LOG_TIME<=to_date('2004-07-04 23:59:59','yyyy-mm-dd HH24:MI:SS')
GROUP BY GAME.name,
GAME_CARD_TYPE.NAME,
V_SALE_TMP_LOG.SALE_MODE
ORDER BY -sum(V_SALE_TMP_LOG.CITY_AGENT_COST) DESC,sum(V_SALE_TMP_LOG.GAME_CARD_NUM) DESC
这个SQL在执行时将2G的TEMP表空间溢出来，通过lecco sql ecpert对该SQL做了分析
SQL> l
1select GAME_CARD_TYPE.NAME,
2GAME.NAME GameName,
3sum(V_SALE_TMP_LOG.GAME_CARD_NUM) as num,
4sum(V_SALE_TMP_LOG.CITY_AGENT_COST) as sumSalePrice,
5sum(V_SALE_TMP_LOG.PROVINCE_AGENT_COST) as basePrice,
6V_SALE_TMP_LOG.SALE_MODE
7 from V_SALE_TMP_LOG,
8GAME_CARD_TYPE,
9GAME,
10RESELLER_BASE,
11AGENT_BASE c,
12AGENT_BASE d
13 where (V_SALE_TMP_LOG.SALE_MODE=2 or V_SALE_TMP_LOG.SALE_MODE=3 or V_SALE_TMP_LOG.SALE_MODE=4)
14 and V_SALE_TMP_LOG.GAME_CARD_TYPE_ID=GAME_CARD_TYPE.GAME_CARD_TYPE_ID(+)
15 and GAME_CARD_TYPE.GAME_ID=GAME.GAME_ID(+)
16 and V_SALE_TMP_LOG.RESELLER_ID=RESELLER_BASE.RESELLER_ID
17 and RESELLER_BASE.AGENT_ID=c.AGENT_ID
18 and c.PARENT_AGENT_ID = d.AGENT_ID
19 and V_SALE_TMP_LOG.IS_SUCCESS='Y'
20 and d.AGENT_ID=52080
21and V_SALE_TMP_LOG.LOG_TIME>=to_date('2004-05-04 00:00:00','yyyy-mm-dd HH24:MI:SS') and V_SALE_TMP_LOG.LOG_TIME<
=to_date('2004-07-04 23:59:59','yyyy-mm-dd HH24:MI:SS')
22 GROUP BY GAME.name,
23GAME_CARD_TYPE.NAME,
24V_SALE_TMP_LOG.SALE_MODE
25 ORDER BY -sum(V_SALE_TMP_LOG.CITY_AGENT_COST) DESC,sum(V_SALE_TMP_LOG.GAME_CARD_NUM) DESC
26*
SQL>
162 rows selected.
Elapsed: 00:00:00.77
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2043 Card=334 Byte
s=26386)
10 SORT (ORDER BY) (Cost=2043 Card=334 Bytes=26386)
21 SORT (GROUP BY) (Cost=2043 Card=334 Bytes=26386)
32 HASH JOIN (OUTER) (Cost=1969 Card=334 Bytes=26386)
43 HASH JOIN (OUTER) (Cost=1964 Card=334 Bytes=21710)
54 NESTED LOOPS (Cost=1959 Card=334 Bytes=16032)
65 NESTED LOOPS (Cost=13 Card=47 Bytes=893)
76 NESTED LOOPS (Cost=4 Card=1 Bytes=12)
87 INDEX (UNIQUE SCAN) OF 'SYS_C0010232' (UNIQU
E) (Cost=1 Card=1 Bytes=4)
97 TABLE access (BY INDEX ROWID) OF 'AGENT_BASE
' (Cost=3 Card=1 Bytes=8)
109 INDEX (RANGE SCAN) OF 'IDX_BASE_AGENT_ID'
(NON-UNIQUE) (Cost=1 Card=1)
116 TABLE ACCESS (BY INDEX ROWID) OF 'RESELLER_BAS
E' (Cost=10 Card=47 Bytes=329)
12 11 INDEX (RANGE SCAN) OF 'IDX_RESELLER_BASE_AGE
NT_ID' (NON-UNIQUE) (Cost=1 Card=47)
135 VIEW OF 'V_SALE_TMP_LOG' (Cost=42 Card=7 Bytes=2
03)
14 13 UNION-ALL (PARTITION)
15 14 TABLE ACCESS (BY INDEX ROWID) OF 'SALE_TMP_L
OG' (Cost=22 Card=26 Bytes=728)
16 15 INDEX (RANGE SCAN) OF 'IDX_RESEID_STL' (NO
N-UNIQUE) (Cost=3 Card=79)
17 14 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'SAL
E_TMP_LOG_DELETED' (Cost=2 Card=1 Bytes=28)
18 17 INDEX (RANGE SCAN) OF 'IDX_SALE_DELETED_LO
G_TIME' (NON-UNIQUE) (Cost=3 Card=1)
194 TABLE ACCESS (FULL) OF 'GAME_CARD_TYPE' (Cost=4 Ca
rd=1084 Bytes=18428)
203 TABLE ACCESS (FULL) OF 'GAME' (Cost=4 Card=218 Bytes
=3052)
Statistics
----------------------------------------------------------
357recursive calls
0db block gets
41519consistent gets
924physical reads
0redo size
7929bytes sent via SQL*Net to client
609bytes received via SQL*Net from client
12SQL*Net roundtrips to/from client
2sorts (memory)
0sorts (disk)
162rows processed
这是源SQL执行后的结果
SQL> l
1select /*+ USE_HASH(V_SALE_TMP_LOG) */ GAME_CARD_TYPE.NAME,
2 GAME.NAME GameName,
3 sum(V_SALE_TMP_LOG.GAME_CARD_NUM) as num,
4 sum(V_SALE_TMP_LOG.CITY_AGENT_COST) as sumSalePrice,
5 sum(V_SALE_TMP_LOG.PROVINCE_AGENT_COST) as basePrice,
6 V_SALE_TMP_LOG.SALE_MODE
7from V_SALE_TMP_LOG,
8 GAME_CARD_TYPE,
9 GAME,
10 RESELLER_BASE,
11 AGENT_BASE c,
12 AGENT_BASE d
13 where (V_SALE_TMP_LOG.SALE_MODE = 2
14 or V_SALE_TMP_LOG.SALE_MODE = 3
15 or V_SALE_TMP_LOG.SALE_MODE = 4)
16 and V_SALE_TMP_LOG.GAME_CARD_TYPE_ID = GAME_CARD_TYPE.GAME_CARD_TYPE_ID (+)
17 and GAME_CARD_TYPE.GAME_ID = GAME.GAME_ID (+)
18 and V_SALE_TMP_LOG.RESELLER_ID = RESELLER_BASE.RESELLER_ID
19 and RESELLER_BASE.AGENT_ID = c.AGENT_ID
20 and c.PARENT_AGENT_ID = d.AGENT_ID
21 and V_SALE_TMP_LOG.IS_SUCCESS = 'Y'
22 and d.AGENT_ID = 52080
23 and V_SALE_TMP_LOG.LOG_TIME >
= to_date('2004-05-04 00:00:00', 'yyyy-mm-dd HH24:MI:SS')
24 and V_SALE_TMP_LOG.LOG_TIME <= to_date('2004-07-04 23:59:59', 'yyyy-mm-dd HH24:MI:SS')
25 GROUP BY GAME.name,
26GAME_CARD_TYPE.NAME,
27V_SALE_TMP_LOG.SALE_MODE
28 ORDER BY -sum(V_SALE_TMP_LOG.CITY_AGENT_COST) DESC,
29sum(V_SALE_TMP_LOG.GAME_CARD_NUM) DESC
30*
162 rows selected.
Elapsed: 00:00:04.09
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost
