今天接到同事求助，說有一個select query，在Oracle上要跑一分多鐘，他希望能在5s內出結果，該sql如下：

Select /*+ parallel(src, 8) */ distinctsrc.systemname as systemname, src.databasename as databasename, src.tablename as tablename, src.username as username from meta_dbql_table_usage_exp_hst srcinner join DR_QRY_LOG_EXP_HST rl onsrc.acctstringdate = rl.acctstringdateand src.queryid = rl.queryidAnd Src.Systemname = Rl.Systemnameand src.acctstringdate > sysdate - 30And Rl.Acctstringdate > Sysdate - 30inner join meta_dr_qry_log_tgt_all_hst tgt onupper(tgt.systemname) = upper('MOZART')And Upper(tgt.Databasename) = Upper('GDW_TABLES')And Upper(tgt.Tablename) = Upper('SSA_SLNG_LSTG_MTRC_SD')AND src.acctstringdate = tgt.acctstringdateand rl.statement_id = tgt.statement_idand rl.systemname = tgt.systemnameAnd Tgt.Acctstringdate > Sysdate - 30And Not(Upper(Tgt.Systemname)=Upper(src.systemname)AndUpper(Tgt.Databasename) = Upper(Src.Databasename)AndUpper(Tgt.Tablename) = Upper(Src.Tablename))And tgt.Systemname is not nullAnd tgt.Databasename Is Not NullAnd tgt.tablename is not null ;

SQL的簡單分析

總得來看，這個SQL就是三個表(meta_dbql_table_usage_exp_hst，DR_QRY_LOG_EXP_HST，meta_dr_qry_log_tgt_all_hst）的INNER JOIN，這三個表數據量都在百萬級別，且都是分區表（以acctstringdate為分區鍵），執行計劃如下： ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 159 | 8654 | | | | 1 | PX COORDINATOR | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 159 | 8654 | | | | 3 | SORT UNIQUE | | 1 | 159 | 8654 | | | | 4 | PX RECEIVE | | 1 | 36 | 3 | | | | 5 | PX SEND HASH | :TQ10001 | 1 | 36 | 3 | | | |* 6 | TABLE ACCESS BY LOCAL INDEX ROWID| DR_QRY_LOG_EXP_HST | 1 | 36 | 3 | | | | 7 | NESTED LOOPS | | 1 | 159 | 8633 | | | | 8 | NESTED LOOPS | | 8959 | 1076K| 4900 | | | | 9 | BUFFER SORT | | | | | | | | 10 | PX RECEIVE | | | | | | | | 11 | PX SEND BROADCAST | :TQ10000 | | | | | | | 12 | PARTITION RANGE ITERATOR | | 1 | 56 | 4746 | KEY | 14 | |* 13 | TABLE ACCESS FULL | META_DR_QRY_LOG_TGT_ALL_HST | 1 | 56 | 4746 | KEY | 14 | | 14 | PX BLOCK ITERATOR | | 8959 | 586K| 154 | KEY | KEY | |* 15 | TABLE ACCESS FULL | META_DBQL_TABLE_USAGE_EXP_HST | 8959 | 586K| 154 | KEY | KEY | | 16 | PARTITION RANGE ITERATOR | | 1 | | 2 | KEY | KEY | |* 17 | INDEX RANGE SCAN | DR_QRY_LOG_EXP_HST_IDX | 1 | | 2 | KEY | KEY | ------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): ---------------------------------------------------6 - filter("RL"."STATEMENT_ID"="TGT"."STATEMENT_ID" AND "RL"."SYSTEMNAME"="TGT"."SYSTEMNAME" AND "SRC"."SYSTEMNAME"="RL"."SYSTEMNAME")13 - filter(UPPER("TGT"."SYSTEMNAME")='MOZART' AND UPPER("TGT"."DATABASENAME")='GDW_TABLES' ANDUPPER("TGT"."TABLENAME")='SSA_SLNG_LSTG_MTRC_SD' AND "TGT"."ACCTSTRINGDATE">SYSDATE@!-30 AND "TGT"."SYSTEMNAME" IS NOT NULL"TGT"."DATABASENAME" IS NOT NULL AND "TGT"."TABLENAME" IS NOT NULL)15 - filter("SRC"."ACCTSTRINGDATE"="TGT"."ACCTSTRINGDATE" AND (UPPER("TGT"."SYSTEMNAME")<>UPPER("SRC"."SYSTEMNAME") ORUPPER("TGT"."DATABASENAME")<>UPPER("SRC"."DATABASENAME") OR UPPER("TGT"."TABLENAME")<>UPPER("SRC"."TABLENAME")) AND"SRC"."ACCTSTRINGDATE">SYSDATE@!-30)17 - access("SRC"."QUERYID"="RL"."QUERYID" AND "SRC"."ACCTSTRINGDATE"="RL"."ACCTSTRINGDATE")filter("RL"."ACCTSTRINGDATE">SYSDATE@!-30)

定位問題

從上面執行計劃中的表連接方式可以知道，這三個表之間進行了兩次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_HST',upper('meta_dbql_table_usage_exp_hst'), upper('meta_dr_qry_log_tgt_all_hsINDEX_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_HSTCREATE 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_IDX2 on gv.DR_QRY_LOG_EXP_HST (statement_id,ACCTSTRINGDATE) local;

性能對比

新的執行計劃如下： ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 159 | 4838 | | | | 1 | SORT UNIQUE | | 1 | 159 | 4838 | | | |* 2 | TABLE ACCESS BY LOCAL INDEX ROWID | META_DBQL_TABLE_USAGE_EXP_HST | 1 | 67 | 3 | | | | 3 | NESTED LOOPS | | 1 | 159 | 4816 | | | | 4 | NESTED LOOPS | | 18 | 1656 | 4762 | | | | 5 | PARTITION RANGE ITERATOR | | 1 | 56 | 4746 | KEY | 14 | |* 6 | TABLE ACCESS FULL | META_DR_QRY_LOG_TGT_ALL_HST | 1 | 56 | 4746 | KEY | 14 | | 7 | PARTITION RANGE ITERATOR | | 18 | 648 | 16 | KEY | 14 | |* 8 | TABLE ACCESS BY LOCAL INDEX ROWID| DR_QRY_LOG_EXP_HST | 18 | 648 | 16 | KEY | 14 | |* 9 | INDEX RANGE SCAN | DR_QRY_LOG_EXP_HST_IDX2 | 31 | | 15 | KEY | 14 | | 10 | PARTITION RANGE ITERATOR | | 1 | | 2 | KEY | KEY | |* 11 | INDEX RANGE SCAN | META_DBQL_TUSAGE_EHST_IDX | 1 | | 2 | KEY | KEY | ------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id): ---------------------------------------------------2 - filter((UPPER("TGT"."SYSTEMNAME")<>UPPER("SRC"."SYSTEMNAME") ORUPPER("TGT"."DATABASENAME")<>UPPER("SRC"."DATABASENAME") OR UPPER("TGT"."TABLENAME")<>UPPER("SRC"."TABLENAME"))AND "SRC"."SYSTEMNAME"="RL"."SYSTEMNAME")6 - filter(UPPER("TGT"."SYSTEMNAME")='MOZART' AND UPPER("TGT"."DATABASENAME")='GDW_TABLES' ANDUPPER("TGT"."TABLENAME")='SSA_SLNG_LSTG_MTRC_SD' AND "TGT"."ACCTSTRINGDATE">SYSDATE@!-30 AND "TGT"."SYSTEMNAME"IS NOT NULL AND "TGT"."DATABASENAME" IS NOT NULL AND "TGT"."TABLENAME" IS NOT NULL)8 - filter("RL"."SYSTEMNAME"="TGT"."SYSTEMNAME")9 - access("RL"."STATEMENT_ID"="TGT"."STATEMENT_ID" AND "RL"."ACCTSTRINGDATE">SYSDATE@!-30 AND"RL"."ACCTSTRINGDATE" IS NOT NULL)11 - access("SRC"."QUERYID"="RL"."QUERYID" AND "SRC"."ACCTSTRINGDATE"="RL"."ACCTSTRINGDATE")filter("SRC"."ACCTSTRINGDATE"="TGT"."ACCTSTRINGDATE" AND "SRC"."ACCTSTRINGDATE">SYSDATE@!-30)
從新的的執行計劃可以看出，它的第一個NESTED LOOP果然用了最新創建的索引。 下面是執行時間： 已用時間: 00: 00: 02.16
兩秒種搞定，遠遠超出他期望的5s ：）

方法總結

NESTED LOOP高效的條件： 驅動數據源有限，且被驅動表在連接列上有相應的索引。

總結

以上是生活随笔為你收集整理的[Oracle] 一个通过添加本地分区索引提高SQL性能的案例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。