一个SQL的优化过程

ORA: unable to extend temp segment by in tablespace TEMP

select GAME_CARD_TYPENAME

GAMENAME GameName

sum(V_SALE_TMP_LOGGAME_CARD_NUM) as num

sum(V_SALE_TMP_LOGCITY_AGENT_COST) as sumSalePrice

sum(V_SALE_TMP_LOGPROVINCE_AGENT_COST) as basePrice

V_SALE_TMP_LOGSALE_MODE

from V_SALE_TMP_LOG

GAME_CARD_TYPE

GAME

RESELLER_BASE

AGENT_BASE c

AGENT_BASE d

where (V_SALE_TMP_LOGSALE_MODE= or V_SALE_TMP_LOGSALE_MODE= or V_SALE_TMP_LOGSALE_MODE=)

and V_SALE_TMP_LOGGAME_CARD_TYPE_ID=GAME_CARD_TYPEGAME_CARD_TYPE_ID(+)

and GAME_CARD_TYPEGAME_ID=GAMEGAME_ID(+)

and V_SALE_TMP_LOGRESELLER_ID=RESELLER_BASERESELLER_ID

and RESELLER_BASEAGENT_ID=cAGENT_ID

and cPARENT_AGENT_ID = dAGENT_ID

and V_SALE_TMP_LOGIS_SUCCESS=Y

and dAGENT_ID=

and V_SALE_TMP_LOGLOG_TIME>=to_date( ::yyyymmdd HH:MI:SS) and V_SALE_TMP_LOGLOG_TIME<=to_date( ::yyyymmdd HH:MI:SS)

GROUP BY GAMEname

GAME_CARD_TYPENAME

V_SALE_TMP_LOGSALE_MODE

ORDER BY sum(V_SALE_TMP_LOGCITY_AGENT_COST) DESCsum(V_SALE_TMP_LOGGAME_CARD_NUM) DESC

这个SQL在执行时将G的TEMP表空间溢出来通过lecco sql ecpert对该SQL做了分析

SQL> l

select GAME_CARD_TYPENAME

GAMENAME GameName

sum(V_SALE_TMP_LOGGAME_CARD_NUM) as num

sum(V_SALE_TMP_LOGCITY_AGENT_COST) as sumSalePrice

sum(V_SALE_TMP_LOGPROVINCE_AGENT_COST) as basePrice

V_SALE_TMP_LOGSALE_MODE

from V_SALE_TMP_LOG

GAME_CARD_TYPE

GAME

RESELLER_BASE

AGENT_BASE c

AGENT_BASE d

where (V_SALE_TMP_LOGSALE_MODE= or V_SALE_TMP_LOGSALE_MODE= or V_SALE_TMP_LOGSALE_MODE=)

and V_SALE_TMP_LOGGAME_CARD_TYPE_ID=GAME_CARD_TYPEGAME_CARD_TYPE_ID(+)

and GAME_CARD_TYPEGAME_ID=GAMEGAME_ID(+)

and V_SALE_TMP_LOGRESELLER_ID=RESELLER_BASERESELLER_ID

and RESELLER_BASEAGENT_ID=cAGENT_ID

and cPARENT_AGENT_ID = dAGENT_ID

and V_SALE_TMP_LOGIS_SUCCESS=Y

and dAGENT_ID=

and V_SALE_TMP_LOGLOG_TIME>=to_date( ::yyyymmdd HH:MI:SS) and V_SALE_TMP_LOGLOG_TIME<=to_date( ::yyyymmdd HH:MI:SS)

GROUP BY GAMEname

GAME_CARD_TYPENAME

V_SALE_TMP_LOGSALE_MODE

ORDER BY sum(V_SALE_TMP_LOGCITY_AGENT_COST) DESCsum(V_SALE_TMP_LOGGAME_CARD_NUM) DESC

*

SQL>

rows selected

Elapsed: ::

Execution Plan

SELECT STATEMENT Optimizer=ALL_ROWS (Cost= Card= Byte

s=)

SORT (ORDER BY) (Cost= Card= Bytes=)

SORT (GROUP BY) (Cost= Card= Bytes=)

HASH JOIN (OUTER) (Cost= Card= Bytes=)

HASH JOIN (OUTER) (Cost= Card= Bytes=)

NESTED LOOPS (Cost= Card= Bytes=)

NESTED LOOPS (Cost= Card= Bytes=)

NESTED LOOPS (Cost= Card= Bytes=)

INDEX (UNIQUE SCAN) OF SYS_C (UNIQU

E) (Cost= Card= Bytes=)

TABLE ACCESS (BY INDEX ROWID) OF AGENT_BASE

(Cost= Card= Bytes=)

INDEX (RANGE SCAN) OF IDX_BASE_AGENT_ID

(NONUNIQUE) (Cost= Card=)

TABLE ACCESS (BY INDEX ROWID) OF RESELLER_BAS

E (Cost= Card= Bytes=)

INDEX (RANGE SCAN) OF IDX_RESELLER_BASE_AGE

NT_ID (NONUNIQUE) (Cost= Card=)

VIEW OF V_SALE_TMP_LOG (Cost= Card= Bytes=

)

UNIONALL (PARTITION)

TABLE ACCESS (BY INDEX ROWID) OF SALE_TMP_L

OG (Cost= Card= Bytes=)

INDEX (RANGE SCAN) OF IDX_RESEID_STL (NO

NUNIQUE) (Cost= Card=)

TABLE ACCESS (BY GLOBAL INDEX ROWID) OF SAL

E_TMP_LOG_DELETED (Cost= Card= Bytes=)

INDEX (RANGE SCAN) OF IDX_SALE_DELETED_LO

G_TIME (NONUNIQUE) (Cost= Card=)

TABLE ACCESS (FULL) OF GAME_CARD_TYPE (Cost= Ca

rd= Bytes=)

TABLE ACCESS (FULL) OF GAME (Cost= Card= Bytes

=)

Statistics

recursive calls

db block gets

consistent gets

physical reads

redo size

bytes sent via SQL*Net to client

bytes received via SQL*Net from client

SQL*Net roundtrips to/from client

sorts (memory)

sorts (disk)

rows processed

这是源SQL执行后的结果

SQL> l

select /*+ USE_HASH(V_SALE_TMP_LOG) */ GAME_CARD_TYPENAME

GAMENAME GameName

sum(V_SALE_TMP_LOGGAME_CARD_NUM) as num

sum(V_SALE_TMP_LOGCITY_AGENT_COST) as sumSalePrice

sum(V_SALE_TMP_LOGPROVINCE_AGENT_COST) as basePrice

V_SALE_TMP_LOGSALE_MODE

from V_SALE_TMP_LOG

GAME_CARD_TYPE

GAME

RESELLER_BASE

AGENT_BASE c

AGENT_BASE d

where (V_SALE_TMP_LOGSALE_MODE =

or V_SALE_TMP_LOGSALE_MODE =

or V_SALE_TMP_LOGSALE_MODE = )

and V_SALE_TMP_LOGGAME_CARD_TYPE_ID = GAME_CARD_TYPEGAME_CARD_TYPE_ID (+)

and GAME_CARD_TYPEGAME_ID = GAMEGAME_ID (+)

and V_SALE_TMP_LOGRESELLER_ID = RESELLER_BASERESELLER_ID

and RESELLER_BASEAGENT_ID = cAGENT_ID

and cPARENT_AGENT_ID = dAGENT_ID

and V_SALE_TMP_LOGIS_SUCCESS = Y

and dAGENT_ID =

and V_SALE_TMP_LOGLOG_TIME >= to_date( :: yyyymmdd HH:MI:SS)

and V_SALE_TMP_LOGLOG_TIME <= to_date( :: yyyymmdd HH:MI:SS)

GROUP BY GAMEname

GAME_CARD_TYPENAME

V_SALE_TMP_LOGSALE_MODE

ORDER BY sum(V_SALE_TMP_LOGCITY_AGENT_COST) DESC

sum(V_SALE_TMP_LOGGAME_CARD_NUM) DESC

*

rows selected

Elapsed: ::

Execution Plan

SELECT STATEMENT Optimizer=ALL_ROWS (Cost