主頁 > 知識庫 > 解析一個通過添加本地分區(qū)索引提高SQL性能的案例

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

熱門標簽:美圖秀秀地圖標注 外呼線路外顯本地號碼 阿爾巴尼亞地圖標注app 人工智能地圖標注自己能做嗎 百度地圖標注素材 征服眼公司地圖標注 征服者火車站地圖標注 word地圖標注方向 開封智能外呼系統(tǒng)廠家

該sql如下:

復(fù)制代碼 代碼如下:

Select  /*+ parallel(src, 8) */ distinct
  src.systemname as systemname
  ,  src.databasename as databasename
  ,  src.tablename as tablename
  ,  src.username as username
from  STRONG>meta_dbql_table_usage_exp_hst/STRONG> src
 inner STRONG>join DR_QRY_LOG_EXP_HST/STRONG> rl on
  STRONG>src.acctstringdate = rl.acctstringdate
  and src.queryid = rl.queryid/STRONG>
  And Src.Systemname = Rl.Systemname
  and src.acctstringdate > sysdate - 30
  And Rl.Acctstringdate > Sysdate - 30
 inner join  STRONG>meta_dr_qry_log_tgt_all_hst /STRONG>tgt on
  upper(tgt.systemname) = upper('MOZART')
  And Upper(tgt.Databasename) = Upper('GDW_TABLES')
  And Upper(tgt.Tablename) = Upper('SSA_SLNG_LSTG_MTRC_SD')
  STRONG>AND src.acctstringdate = tgt.acctstringdate
  and rl.statement_id = tgt.statement_id/STRONG>
  and rl.systemname = tgt.systemname
  And Tgt.Acctstringdate > Sysdate - 30
  And Not(
    Upper(Tgt.Systemname)=Upper(src.systemname)
    And
    Upper(Tgt.Databasename) = Upper(Src.Databasename)
    And
    Upper(Tgt.Tablename) = Upper(Src.Tablename)
    )
  And   tgt.Systemname is not null
  And   tgt.Databasename Is Not Null
  And   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,這三個表數(shù)據(jù)量都在百萬級別,且都是分區(qū)表(以acctstringdate為分區(qū)鍵),執(zhí)行計劃如下:
復(fù)制代碼 代碼如下:

------------------------------------------------------------------------------------------------------------------------
| 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' AND
              UPPER("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") OR
              UPPER("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)

定位問題
從上面執(zhí)行計劃中的表連接方式可以知道,這三個表之間進行了兩次NESTED LOOP,問題出現(xiàn)在最里層的NESTED LOOP(對兩個表都做了TABLE FULL SCAN),因為表都是百萬級別的(即時過濾后的數(shù)據(jù)量也不?。阅軉栴}就出現(xiàn)在內(nèi)表(即被驅(qū)動表)META_DBQL_TABLE_USAGE_EXP_HST做了太多次的全表掃描。如果能把全表掃描轉(zhuǎn)換成索引,則性能可以大幅度提高。

下面是NESTED LOOP的介紹:
嵌套連接把要處理的數(shù)據(jù)集分為外部循環(huán)(驅(qū)動數(shù)據(jù)源)和內(nèi)部循環(huán)(被驅(qū)動數(shù)據(jù)源),外部循環(huán)只執(zhí)行一次,內(nèi)部循環(huán)執(zhí)行的次數(shù)等于外部循環(huán)執(zhí)行返回的數(shù)據(jù)個數(shù)。
這種連接的好處是內(nèi)存使用非常少。
如果驅(qū)動數(shù)據(jù)源有限,且被驅(qū)動表在連接列上有相應(yīng)的索引,則這種連接方式才是高效的。

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

復(fù)制代碼 代碼如下:

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_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")

這三個索引都是本地分區(qū)索引(都包含分區(qū)鍵acctstringdate),很顯然,DR_QRY_LOG_EXP_HST表少了個索引,因為它與表meta_dr_qry_log_tgt_all_hst 在statement_id上做join,因此應(yīng)該在它的statement_id上也創(chuàng)建本地分區(qū)索引如下:
復(fù)制代碼 代碼如下:

create index DR_QRY_LOG_EXP_HST_IDX2 on gv.DR_QRY_LOG_EXP_HST (statement_id,ACCTSTRINGDATE) local;

性能對比
新的執(zhí)行計劃如下:
復(fù)制代碼 代碼如下:

------------------------------------------------------------------------------------------------------------------------
| 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 |        STRONG>INDEX RANGE SCAN                | DR_QRY_LOG_EXP_HST_IDX2/STRONG>       |    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") OR
              UPPER("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' AND
              UPPER("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)

從新的的執(zhí)行計劃可以看出,它的第一個NESTED LOOP果然用了最新創(chuàng)建的索引。
下面是執(zhí)行時間:
復(fù)制代碼 代碼如下:

已用時間:  00: 00: 02.16

兩秒種搞定,遠遠超出他期望的5s :)
方法總結(jié)
NESTED LOOP高效的條件:驅(qū)動數(shù)據(jù)源有限,且被驅(qū)動表在連接列上有相應(yīng)的索引。

標簽:泰安 淮南 宜春 酒泉 葫蘆島 孝感 海北 六安

巨人網(wǎng)絡(luò)通訊聲明:本文標題《解析一個通過添加本地分區(qū)索引提高SQL性能的案例》,本文關(guān)鍵詞  解析,一個,通過,添加,本地,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《解析一個通過添加本地分區(qū)索引提高SQL性能的案例》相關(guān)的同類信息!
  • 本頁收集關(guān)于解析一個通過添加本地分區(qū)索引提高SQL性能的案例的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章