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

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

　　該sql如下

復制代碼代碼如下:
Select /*+ parallel(src ) */ distinct
srcsystemname as systemname
srcdatabasename as databasename
srctablename as tablename
srcusername as username
from meta_dbql_table_usage_exp_hst src
inner join DR_QRY_LOG_EXP_HST rl on
srcacctstringdate = rlacctstringdate
and srcqueryid = rlqueryid
And SrcSystemname = RlSystemname
and srcacctstringdate > sysdate
And RlAcctstringdate > Sysdate
inner join meta_dr_qry_log_tgt_all_hst tgt on
upper(tgtsystemname) = upper(MOZART)
And Upper(tgtDatabasename) = Upper(GDW_TABLES)
And Upper(tgtTablename) = Upper(SSA_SLNG_LSTG_MTRC_SD)
AND srcacctstringdate = tgtacctstringdate
and rlstatement_id = tgtstatement_id
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;

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

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

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

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

From:http://tw.wingwit.com/Article/program/Oracle/201311/19032.html