解析一個(gè)通過(guò)添加本地分區(qū)索引提高SQL性能的案例
更新時(shí)間：2013年07月12日 12:32:26 作者：
今天接到同事求助，說(shuō)有一個(gè)select query，在Oracle上要跑一分多鐘，他希望能在5s內(nèi)出結(jié)果，以下就是解決這個(gè)問(wèn)題的方法，需要的朋友可以參考下
該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的簡(jiǎn)單分析
總得來(lái)看，這個(gè)SQL就是三個(gè)表(meta_dbql_table_usage_exp_hst，DR_QRY_LOG_EXP_HST，meta_dr_qry_log_tgt_all_hst）的INNER JOIN，這三個(gè)表數(shù)據(jù)量都在百萬(wàn)級(jí)別，且都是分區(qū)表（以acctstringdate為分區(qū)鍵），執(zhí)行計(jì)劃如下：
復(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)
定位問(wèn)題
從上面執(zhí)行計(jì)劃中的表連接方式可以知道，這三個(gè)表之間進(jìn)行了兩次NESTED LOOP，問(wèn)題出現(xiàn)在最里層的NESTED LOOP（對(duì)兩個(gè)表都做了TABLE FULL SCAN），因?yàn)楸矶际前偃f(wàn)級(jí)別的（即時(shí)過(guò)濾后的數(shù)據(jù)量也不?。阅軉?wèn)題就出現(xiàn)在內(nèi)表（即被驅(qū)動(dòng)表）META_DBQL_TABLE_USAGE_EXP_HST做了太多次的全表掃描。如果能把全表掃描轉(zhuǎn)換成索引，則性能可以大幅度提高。
下面是NESTED LOOP的介紹：
嵌套連接把要處理的數(shù)據(jù)集分為外部循環(huán)（驅(qū)動(dòng)數(shù)據(jù)源）和內(nèi)部循環(huán)（被驅(qū)動(dòng)數(shù)據(jù)源），外部循環(huán)只執(zhí)行一次，內(nèi)部循環(huán)執(zhí)行的次數(shù)等于外部循環(huán)執(zhí)行返回的數(shù)據(jù)個(gè)數(shù)。
這種連接的好處是內(nèi)存使用非常少。
如果驅(qū)動(dòng)數(shù)據(jù)源有限，且被驅(qū)動(dòng)表在連接列上有相應(yīng)的索引，則這種連接方式才是高效的。
下面是這三個(gè)表上索引的情況：
復(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")
這三個(gè)索引都是本地分區(qū)索引（都包含分區(qū)鍵acctstringdate），很顯然，DR_QRY_LOG_EXP_HST表少了個(gè)索引，因?yàn)樗c表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;
性能對(duì)比
新的執(zhí)行計(jì)劃如下：
復(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í)行計(jì)劃可以看出，它的第一個(gè)NESTED LOOP果然用了最新創(chuàng)建的索引。
下面是執(zhí)行時(shí)間：
復(fù)制代碼 代碼如下:
已用時(shí)間: 00: 00: 02.16
兩秒種搞定，遠(yuǎn)遠(yuǎn)超出他期望的5s ：）
方法總結(jié)
NESTED LOOP高效的條件：驅(qū)動(dòng)數(shù)據(jù)源有限，且被驅(qū)動(dòng)表在連接列上有相應(yīng)的索引。
相關(guān)文章
這篇文章主要介紹了如何在Oracle中導(dǎo)入dmp文件,很簡(jiǎn)單，但很實(shí)用，需要的朋友可以參考下2014-09-09
Oracle 12.2處理sysaux空間占滿(mǎn)問(wèn)題
今天處理別的問(wèn)題查看告警日志偶然發(fā)現(xiàn)大量的報(bào)錯(cuò),無(wú)法擴(kuò)展SYSAUX表空間,于是登錄系統(tǒng),查看系統(tǒng)表空間使用情況,發(fā)現(xiàn)SYSAUX表空間用滿(mǎn)了,所以本文給大家介紹了Oracle 12.2處理sysaux空間占滿(mǎn)問(wèn)題,需要的朋友可以參考下2024-02-02
Oracle9i在Win2k環(huán)境下的完全卸載...2007-03-03
Oracle ASM故障數(shù)據(jù)恢復(fù)解決方案
在本篇文章里小編給大家整理的是關(guān)于Oracle ASM故障數(shù)據(jù)恢復(fù)解決方案以及相關(guān)知識(shí)點(diǎn)，有需要的朋友們參考下。2019-11-11
這篇文章介紹了Oracle中鎖（lock）的用法?，文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-05-05
這篇文章主要介紹了Oracle - 如何將多張表中的列合并到一張表中,需要的朋友可以參考下2019-11-11
這篇文章主要為大家詳細(xì)介紹了win7下oracle 10g安裝圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-03-03
Oracle停止數(shù)據(jù)泵導(dǎo)入數(shù)據(jù)的方法詳解
Oracle數(shù)據(jù)庫(kù)在使用的過(guò)程中常常會(huì)遇到這樣或那樣的問(wèn)題,而這些問(wèn)題常常又使我們感到很困惑,下面這篇文章主要給大家介紹了關(guān)于Oracle停止數(shù)據(jù)泵導(dǎo)入數(shù)據(jù)的相關(guān)資料,需要的朋友可以參考下2022-06-06
在oracle 數(shù)據(jù)庫(kù)中查看一個(gè)sql語(yǔ)句的執(zhí)行時(shí)間和SP2-0027錯(cuò)誤
oracle 數(shù)據(jù)庫(kù)中查看一個(gè)sql語(yǔ)句的執(zhí)行時(shí)間和SP2-0027錯(cuò)誤的解決方法2009-10-10
這篇文章主要介紹了SQLPlus命令操作用法詳解,本篇文章通過(guò)簡(jiǎn)要的案例,講解了該項(xiàng)技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-08-08