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

解析一個通過添加本地分區索引提高SQL性能的案例

2013-11-13 22:24:43  來源: Oracle 
今天接到同事求助說有一個select query在Oracle上要跑一分多鐘他希望能在s內出結果以下就是解決這個問題的方法需要的朋友可以參考下  

  該sql如下

復制代碼 代碼如下:
Select  /*+ parallel(src ) */ distinct
  srcsystemname as systemname
    srcdatabasename as databasename
    srctablename as tablename
    srcusername as username
from  <STRONG>meta_dbql_table_usage_exp_hst</STRONG> src
 inner <STRONG>join DR_QRY_LOG_EXP_HST</STRONG> rl on
  <STRONG>srcacctstringdate = rlacctstringdate
  and srcqueryid = rlqueryid</STRONG>
  And SrcSystemname = RlSystemname
  and srcacctstringdate > sysdate
  And RlAcctstringdate > Sysdate
 inner join  <STRONG>meta_dr_qry_log_tgt_all_hst </STRONG>tgt on
  upper(tgtsystemname) = upper(MOZART)
  And Upper(tgtDatabasename) = Upper(GDW_TABLES)
  And Upper(tgtTablename) = Upper(SSA_SLNG_LSTG_MTRC_SD)
  <STRONG>AND srcacctstringdate = tgtacctstringdate
  and rlstatement_id = tgtstatement_id</STRONG>
  and rlsystemname = tgtsystemname
  And TgtAcctstringdate > Sysdate
  And Not(
    Upper(TgtSystemname)=Upper(srcsystemname)
    And
    Upper(TgtDatabasename) = Upper(SrcDatabasename)
    And
    Upper(TgtTablename) = Upper(SrcTablename)
    )
  And   tgtSystemname is not null
  And   tgtDatabasename Is Not Null
  And   tgttablename is not null
 

  
SQL的簡單分析
總 得來看這個SQL就是三個表 (meta_dbql_table_usage_exp_hstDR_QRY_LOG_EXP_HSTmeta_dr_qry_log_tgt_all_hst) 的INNER JOIN這三個表數據量都在百萬級別且都是分區表(以acctstringdate為分區鍵)執行計劃如下

復制代碼 代碼如下:

| Id  | Operation                              | Name                          | Rows  | Bytes | Cost  | Pstart| Pstop |

|   | SELECT STATEMENT                       |                               |     |   |       |       |
|   |  PX COORDINATOR                        |                               |       |       |       |       |       |
|   |   PX SEND QC (RANDOM)                  | :TQ                      |     |   |       |       |
|   |    SORT UNIQUE                         |                               |     |   |       |       |
|   |     PX RECEIVE                         |                               |     |    |     |       |       |
|   |      PX SEND HASH                      | :TQ                      |     |    |     |       |       |
|*  |       TABLE ACCESS BY LOCAL INDEX ROWID| DR_QRY_LOG_EXP_HST            |     |    |     |       |       |
|   |        NESTED LOOPS                    |                               |     |   |       |       |
|   |         NESTED LOOPS                   |                               |  K|  |       |       |
|   |          BUFFER SORT                   |                               |       |       |       |       |       |
|           PX RECEIVE                   |                               |       |       |       |       |       |
|            PX SEND BROADCAST           | :TQ                      |       |       |       |       |       |
|             PARTITION RANGE ITERATOR   |                               |     |    |   KEY |    |
|* |              TABLE ACCESS FULL         | META_DR_QRY_LOG_TGT_ALL_HST   |     |    |   KEY |    |
|          PX BLOCK ITERATOR             |                               |  |   K|   |   KEY |   KEY |
|* |           TABLE ACCESS FULL            | META_DBQL_TABLE_USAGE_EXP_HST |  |   K|   |   KEY |   KEY |
|         PARTITION RANGE ITERATOR       |                               |     |       |     |   KEY |   KEY |
|* |          INDEX RANGE SCAN              | DR_QRY_LOG_EXP_HST_IDX        |     |       |     |   KEY |   KEY |

Predicate Information (identified by operation id):

   filter("RL""STATEMENT_ID"="TGT""STATEMENT_ID" AND "RL""SYSTEMNAME"="TGT""SYSTEMNAME" AND "SRC""SYSTEMNAME"="RL""SYSTEMNAME")
  filter(UPPER("TGT""SYSTEMNAME")=MOZART AND UPPER("TGT""DATABASENAME")=GDW_TABLES AND
              UPPER("TGT""TABLENAME")=SSA_SLNG_LSTG_MTRC_SD AND "TGT""ACCTSTRINGDATE">SYSDATE@! AND "TGT""SYSTEMNAME" IS NOT NULL
              "TGT""DATABASENAME" IS NOT NULL AND "TGT""TABLENAME" IS NOT NULL)
  filter("SRC""ACCTSTRINGDATE"="TGT""ACCTSTRINGDATE" AND (UPPER("TGT""SYSTEMNAME")<>UPPER("SRC""SYSTEMNAME") OR
              UPPER("TGT""DATABASENAME")<>UPPER("SRC""DATABASENAME") OR UPPER("TGT""TABLENAME")<>UPPER("SRC""TABLENAME")) AND
              "SRC""ACCTSTRINGDATE">SYSDATE@!)
  access("SRC""QUERYID"="RL""QUERYID" AND "SRC""ACCTSTRINGDATE"="RL""ACCTSTRINGDATE")
       filter("RL""ACCTSTRINGDATE">SYSDATE@!)

  
定位問題
從 上面執行計劃中的表連接方式可以知道這三個表之間進行了兩次NESTED LOOP問題出現在最裡層的NESTED LOOP(對兩個表都做了TABLE FULL SCAN)因為表都是百萬級別的(即時過濾後的數據量也不小)性能問題就出現在內表(即被驅動 表)META_DBQL_TABLE_USAGE_EXP_HST做了太多次的全表掃描如果能把全表掃描轉換成索引則性能可以大幅度提高

  下面是NESTED LOOP的介紹
嵌套連接把要處理的數據集分為外部循環(驅動數據源)和內部循環(被驅動數據源)外部循環只執行一次內部循環執行的次數等於外部循環執行返回的數據個數
這種連接的好處是內存使用非常少
如果驅動數據源有限且被驅動表在連接列上有相應的索引則這種連接方式才是高效的

下面是這三個表上索引的情況

復制代碼 代碼如下:
SQL> select index_name table_name from user_indexes where table_name in (DR_QRY_LOG_EXP_HSTupper(meta_dbql_table_usage_exp_hst) upper(meta_dr_qry_log_tgt_all_hs
INDEX_NAME                                                   TABLE_NAME

META_DR_QRY_LOG_TGT_ALL_IDX                                  META_DR_QRY_LOG_TGT_ALL_HST
META_DBQL_TUSAGE_EHST_IDX                                    META_DBQL_TABLE_USAGE_EXP_HST
DR_QRY_LOG_EXP_HST_IDX                                       DR_QRY_LOG_EXP_HST
CREATE INDEX "GV""META_DR_QRY_LOG_TGT_ALL_IDX" ON "GV""META_DR_QRY_LOG_TGT_ALL_HST" ("STATEMENT_ID" "ACCTSTRINGDATE")
CREATE INDEX "GV""META_DBQL_TUSAGE_EHST_IDX" ON "GV""META_DBQL_TABLE_USAGE_EXP_HST" ("QUERYID" "ACCTSTRINGDATE")
CREATE INDEX "GV""DR_QRY_LOG_EXP_HST_IDX" ON "GV""DR_QRY_LOG_EXP_HST" ("QUERYID" "ACCTSTRINGDATE")

  
這 三個索引都是本地分區索引(都包含分區鍵acctstringdate)很顯然DR_QRY_LOG_EXP_HST表少了個索引因為它與表 meta_dr_qry_log_tgt_all_hst 在statement_id上做join因此應該在它的statement_id上也創建本地分區索引如下

復制代碼 代碼如下:
create index DR_QRY_LOG_EXP_HST_IDX on gvDR_QRY_LOG_EXP_HST (statement_idACCTSTRINGDATE) local;

  
性能對比
新的執行計劃如下

復制代碼 代碼如下:

| Id  | Operation                              | Name                          | Rows  | Bytes | Cost  | Pstart| Pstop |

|   | SELECT STATEMENT                       |                               |     |   |       |       |
|   |  SORT UNIQUE                           |                               |     |   |       |       |
|*  |   TABLE ACCESS BY LOCAL INDEX ROWID    | META_DBQL_TABLE_USAGE_EXP_HST |     |    |     |       |       |
|   |    NESTED LOOPS                        |                               |     |   |       |       |
|   |     NESTED LOOPS                       |                               |    |       |       |
|   |      PARTITION RANGE ITERATOR          |                               |     |    |   KEY |    |
|*  |       TABLE ACCESS FULL                | META_DR_QRY_LOG_TGT_ALL_HST   |     |    |   KEY |    |
|   |      PARTITION RANGE ITERATOR          |                               |    |   |    |   KEY |    |
|*  |       TABLE ACCESS BY LOCAL INDEX ROWID| DR_QRY_LOG_EXP_HST            |    |   |    |   KEY |    |
|*  |        <STRONG>INDEX RANGE SCAN                | DR_QRY_LOG_EXP_HST_IDX</STRONG>       |    |       |    |   KEY |    |
|     PARTITION RANGE ITERATOR           |                               |     |       |     |   KEY |   KEY |
|* |      INDEX RANGE SCAN                  | META_DBQL_TUSAGE_EHST_IDX     |     |       |     |   KEY |   KEY |

Predicate Information (identified by operation id):

   filter((UPPER("TGT""SYSTEMNAME")<>UPPER("SRC""SYSTEMNAME") OR
              UPPER("TGT""DATABASENAME")<>UPPER("SRC""DATABASENAME") OR UPPER("TGT""TABLENAME")<>UPPER("SRC""TABLENAME"))
              AND "SRC""SYSTEMNAME"="RL""SYSTEMNAME")
   filter(UPPER("TGT""SYSTEMNAME")=MOZART AND UPPER("TGT""DATABASENAME")=GDW_TABLES AND
              UPPER("TGT""TABLENAME")=SSA_SLNG_LSTG_MTRC_SD AND "TGT""ACCTSTRINGDATE">SYSDATE@! AND "TGT""SYSTEMNAME"
              IS NOT NULL AND "TGT""DATABASENAME" IS NOT NULL AND "TGT""TABLENAME" IS NOT NULL)
   filter("RL""SYSTEMNAME"="TGT""SYSTEMNAME")
   access("RL""STATEMENT_ID"="TGT""STATEMENT_ID" AND "RL""ACCTSTRINGDATE">SYSDATE@! AND
              "RL""ACCTSTRINGDATE" IS NOT NULL)
  access("SRC""QUERYID"="RL""QUERYID" AND "SRC""ACCTSTRINGDATE"="RL""ACCTSTRINGDATE")
       filter("SRC""ACCTSTRINGDATE"="TGT""ACCTSTRINGDATE" AND "SRC""ACCTSTRINGDATE">SYSDATE@!)

  
從新的的執行計劃可以看出它的第一個NESTED LOOP果然用了最新創建的索引
下面是執行時間

復制代碼 代碼如下:
已用時間:  : :

  
兩秒種搞定遠遠超出他期望的s
方法總結
NESTED LOOP高效的條件驅動數據源有限且被驅動表在連接列上有相應的索引


From:http://tw.wingwit.com/Article/program/Oracle/201311/19032.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.