分享
 
 
 

一个SQL的优化过程

王朝mssql·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有