熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

一個SQL的優化過程

2013-11-13 22:13:09  來源: Oracle 

  
  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
From:http://tw.wingwit.com/Article/program/Oracle/201311/18515.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.