一个SQL的优化过程

2/9/2008来源:Oracle教程人气:8097


  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的优化过程

  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