一个SQL的优化过程

　　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表空间溢出来，通过leclearcase/" target="_blank" >cco sql ecpert对该SQL做了分析
　　
　　SQL> l
　　1　select 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
　　7　　　　　　　　　　　　　 from 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 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
　　21　　　　　　　　　　　　　　　　　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')
　　22　　　　　　　　　　　　　 GROUP BY GAME.name,
　　23　　　　　　　　　　　　　　　　　　GAME_CARD_TYPE.NAME,
　　24　　　　　　　　　　　　　　　　　　V_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
　　----------------------------------------------------------
　　0　　　SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2043 Card=334 Byte
　　s=26386)
　　
　　1　　0　 SORT (ORDER BY) (Cost=2043 Card=334 Bytes=26386)
　　2　　1　　 SORT (GROUP BY) (Cost=2043 Card=334 Bytes=26386)
　　3　　2　　　 HASH JOIN (OUTER) (Cost=1969 Card=334 Bytes=26386)
　　4　　3　　　　 HASH JOIN (OUTER) (Cost=1964 Card=334 Bytes=21710)
　　5　　4　　　　　 NESTED LOOPS (Cost=1959 Card=334 Bytes=16032)
　　6　　5　　　　　　 NESTED LOOPS (Cost=13 Card=47 Bytes=893)
　　7　　6　　　　　　　 NESTED LOOPS (Cost=4 Card=1 Bytes=12)
　　8　　7　　　　　　　　 INDEX (UNIQUE SCAN) OF 'SYS_C0010232' (UNIQU
　　E) (Cost=1 Card=1 Bytes=4)
　　
　　9　　7　　　　　　　　 TABLE ACCESS (BY INDEX ROWID) OF 'AGENT_BASE
　　' (Cost=3 Card=1 Bytes=8)
　　
　　10　　9　　　　　　　　　 INDEX (RANGE SCAN) OF 'IDX_BASE_AGENT_ID'
　　(NON-UNIQUE) (Cost=1 Card=1)
　　
　　11　　6　　　　　　　 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)
　　
　　13　　5　　　　　　 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)
　　
　　19　　4　　　　　 TABLE ACCESS (FULL) OF 'GAME_CARD_TYPE' (Cost=4 Ca
　　rd=1084 Bytes=18428)
　　
　　20　　3　　　　 TABLE ACCESS (FULL) OF 'GAME' (Cost=4 Card=218 Bytes
　　=3052)
　　
　　Statistics
　　----------------------------------------------------------
　　357　recursive calls
　　0　db block gets
　　41519　consistent gets
　　924　physical reads
　　0　redo size
　　7929　bytes sent via SQL*Net to client
　　609　bytes received via SQL*Net from client
　　12　SQL*Net roundtrips to/from client
　　2　sorts (memory)
　　0　sorts (disk)
　　162　rows processed
　　
　　这是源SQL执行后的结果
　　
　　SQL> l
　　1　select /*+ 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
　　7　　from 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,
　　26　　　　　　GAME_CARD_TYPE.NAME,
　　27　　　　　　V_SALE_TMP_LOG.SALE_MODE
　　28　 ORDER BY -sum(V_SALE_TMP_LOG.CITY_AGENT_COST) DESC,
　　29　　　　　　sum(V_SALE_TMP_LOG.GAME_CARD_NUM) DESC
　　30*
　　
　　162 rows selected.
　　
　　Elapsed: 00:00:04.09
　　
　　Execution Plan
　　----------------------------------------------------------
　　0　　　SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7072 Card=334 Byte
　　s=26386)
　　
　　1　　0　 SORT (ORDER BY) (Cost=7072 Card=334 Bytes=26386)
　　2　　1　　 SORT (GROUP BY) (Cost=7072 Card=334 Bytes=26386)
　　3　　2　　　 HASH JOIN (OUTER) (Cost=6998 Card=334 Bytes=26386)
　　4　　3　　　　 HASH JOIN (OUTER) (Cost=6993 Card=334 Bytes=21710)
　　5　　4　　　　　 HASH JOIN (Cost=6988 Card=334 Bytes=16032)
　　6　　5　　　　　　 TABLE ACCESS (BY INDEX ROWID) OF 'RESELLER_BASE'
　　(Cost=10 Card=47 Bytes=329)
　　
　　7　　6　　　　　　　 NESTED LOOPS (Cost=13 Card=47 Bytes=893)
　　8　　7　　　　　　　　 NESTED LOOPS (Cost=4 Card=1 Bytes=12)
　　9　　8　　　　　　　　　 INDEX (UNIQUE SCAN) OF 'SYS_C0010232' (UNI
　　QUE) (Cost=1 Card=1 Bytes=4)
　　
　　10　　8　　　　　　　　　 TABLE ACCESS (BY INDEX ROWID) OF 'AGENT_BA
　　SE' (Cost=3 Card=1 Bytes=8)
　　
　　11　 10　　　　　　　　　　 INDEX (RANGE SCAN) OF 'IDX_BASE_AGENT_ID
　　' (NON-UNIQUE) (Cost=1 Card=1)
　　
　　12　　7　　　　　　　　 INDEX (RANGE SCAN) OF 'IDX_RESELLER_BASE_AGE
　　NT_ID' (NON-UNIQUE) (Cost=1 Card=47)
　　
　　13　　5　　　　　　 VIEW OF 'V_SALE_TMP_LOG' (Cost=6974 Card=311466
　　Bytes=9032514)
　　
　　14　 13　　　　　　　 UNION-ALL (PARTITION)
　　15　 14　　　　　　　　 TABLE ACCESS (BY INDEX ROWID) OF 'SALE_TMP_L
　　OG' (Cost=162 Card=417579 Bytes=11692212)
　　
　　16　 15　　　　　　　　　 INDEX (RANGE SCAN) OF 'IDX_SALE_TMP_LOG_LO
　　G_TIME' (NON-UNIQUE) (Cost=18 Card=835157)
　　
　　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)
　　
　　19　　4　　　　　 TABLE ACCESS (FULL) OF 'GAME_CARD_TYPE' (Cost=4 Ca
　　rd=1084 Bytes=18428)
　　
　　20　　3　　　　 TABLE ACCESS (FULL) OF 'GAME' (Cost=4 Card=218 Bytes
　　=3052)
　　
　　Statistics
　　----------------------------------------------------------
　　357　recursive calls
　　0　db block gets
　　455821　consistent gets
　　42　physical reads
　　0　redo size
　　7929　bytes sent via SQL*Net to client
　　609　bytes received via SQL*Net from client
　　12　SQL*Net roundtrips to/from client
　　2　sorts (memory)
　　0　sorts (disk)
　　162　rows processed
　　
　　这个是Lecco sql expert的最优的结果集
　　执行时间爆长，physical read下降了不少
　　
　　SQL> l
　　1　select /*+ USE_MERGE(V_SALE_TMP_LOG, GAME_CARD_TYPE, GAME) */ 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
　　7　　from 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,
　　26　　　　　　GAME_CARD_TYPE.NAME,
　　27　　　　　　V_SALE_TMP_LOG.SALE_MODE
　　28　 ORDER BY -sum(V_SALE_TMP_LOG.CITY_AGENT_COST) DESC,
　　29*　　　　　 sum(V_SALE_TMP_LOG.GAME_CARD_NUM) DESC
　　
　　Elapsed: 00:00:00.54
　　
　　Execution Plan
　　----------------------------------------------------------
　　0　　　SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2185 Card=334 Byte
　　s=26386)
　　
　　1　　0　 SORT (ORDER BY) (Cost=2185 Card=334 Bytes=26386)
　　2　　1　　 SORT (GROUP BY) (Cost=2185 Card=334 Bytes=26386)
　　3　　2　　　 MERGE JOIN (OUTER) (Cost=2111 Card=334 Bytes=26386)
　　4　　3　　　　 SORT (JOIN) (Cost=2073 Card=334 Bytes=21710)
　　5　　4　　　　　 MERGE JOIN (OUTER) (Cost=2036 Card=334 Bytes=21710
　　)
　　
　　6　　5　　　　　　 SORT (JOIN) (Cost=1995 Card=334 Bytes=16032)
　　7　　6　　　　　　　 NESTED LOOPS (Cost=1959 Card=334 Bytes=16032)
　　8　　7　　　　　　　　 NESTED LOOPS (Cost=13 Card=47 Bytes=893)
　　9　　8　　　　　　　　　 NESTED LOOPS (Cost=4 Card=1 Bytes=12)
　　10　　9　　　　　　　　　　 INDEX (UNIQUE SCAN) OF 'SYS_C0010232' (U
　　NIQUE) (Cost=1 Card=1 Bytes=4)
　　
　　11　　9　　　　　　　　　　 TABLE ACCESS (BY INDEX ROWID) OF 'AGENT_
　　BASE' (Cost=3 Card=1 Bytes=8)
　　
　　12　 11　　　　　　　　　　　 INDEX (RANGE SCAN) OF 'IDX_BASE_AGENT_
　　ID' (NON-UNIQUE) (Cost=1 Card=1)
　　
　　13　　8　　　　　　　　　 TABLE ACCESS (BY INDEX ROWID) OF 'RESELLER
　　_BASE' (Cost=10 Card=47 Bytes=329)
　　
　　14　 13　　　　　　　　　　 INDEX (RANGE SCAN) OF 'IDX_RESELLER_BASE
　　_AGENT_ID' (NON-UNIQUE) (Cost=1 Card=47)
　　
　　15　　7　　　　　　　　 VIEW OF 'V_SALE_TMP_LOG' (Cost=42 Card=7 Byt
　　es=203)
　　
　　16　 15　　　　　　　　　 UNION-ALL (PARTITION)
　　17　 16　　　　　　　　　　 TABLE ACCESS (BY INDEX ROWID) OF 'SALE_T
　　MP_LOG' (Cost=22 Card=26 Bytes=728)
　　
　　18　 17　　　　　　　　　　　 INDEX (RANGE SCAN) OF 'IDX_RESEID_STL'
　　(NON-UNIQUE) (Cost=3 Card=79)
　　
　　19　 16　　　　　　　　　　 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF
　　'SALE_TMP_LOG_DELETED' (Cost=2 Card=1 Bytes=28)
　　
　　20　 19　　　　　　　　　　　 INDEX (RANGE SCAN) OF 'IDX_SALE_DELETE
　　D_LOG_TIME' (NON-UNIQUE) (Cost=3 Card=1)
　　
　　21　　5　　　　　　 SORT (JOIN) (Cost=41 Card=1084 Bytes=18428)
　　22　 21　　　　　　　 TABLE ACCESS (FULL) OF 'GAME_CARD_TYPE' (Cost=
　　4 Card=1084 Bytes=18428)
　　
　　23　　3　　　　 SORT (JOIN) (Cost=38 Card=218 Bytes=3052)
　　24　 23　　　　　 TABLE ACCESS (FULL) OF 'GAME' (Cost=4 Card=218 Byt
　　es=3052)
　　
　　Statistics
　　----------------------------------------------------------
　　357　recursive calls
　　0　db block gets
　　41520　consistent gets
　　0　physical reads
　　0　redo size
　　7929　bytes sent via SQL*Net to client
　　609　bytes received via SQL*Net from client
　　12　SQL*Net roundtrips to/from client
　　6　sorts (memory)
　　0　sorts (disk)
　　162　rows processed
　　
　　这是SQL Lecco推荐的另一个SQL，偶也是试了很久才找出来的
　　执行时间与PHYICAL READ等大符下载，但sorts(memory)增多不秒。

原文转自：http://www.ltesting.net