解析一个通过添加本地分区索引提高SQL性能的案例
2023-06-13 09:15:03 时间
该sql如下：
Select /*+parallel(src,8)*/distinct
src.systemnameassystemname
, src.databasenameasdatabasename
, src.tablenameastablename
, src.usernameasusername
from <STRONG>meta_dbql_table_usage_exp_hst</STRONG>src
inner<STRONG>joinDR_QRY_LOG_EXP_HST</STRONG>rlon
<STRONG>src.acctstringdate=rl.acctstringdate
andsrc.queryid=rl.queryid</STRONG>
AndSrc.Systemname=Rl.Systemname
andsrc.acctstringdate>sysdate-30
AndRl.Acctstringdate>Sysdate-30
innerjoin <STRONG>meta_dr_qry_log_tgt_all_hst</STRONG>tgton
upper(tgt.systemname)=upper("MOZART")
AndUpper(tgt.Databasename)=Upper("GDW_TABLES")
AndUpper(tgt.Tablename)=Upper("SSA_SLNG_LSTG_MTRC_SD")
<STRONG>ANDsrc.acctstringdate=tgt.acctstringdate
andrl.statement_id=tgt.statement_id</STRONG>
andrl.systemname=tgt.systemname
AndTgt.Acctstringdate>Sysdate-30
AndNot(
Upper(Tgt.Systemname)=Upper(src.systemname)
And
Upper(Tgt.Databasename)=Upper(Src.Databasename)
And
Upper(Tgt.Tablename)=Upper(Src.Tablename)
)
And tgt.Systemnameisnotnull
And tgt.DatabasenameIsNotNull
And tgt.tablenameisnotnull
SQL的简单分析
总得来看，这个SQL就是三个表(meta_dbql_table_usage_exp_hst，DR_QRY_LOG_EXP_HST，meta_dr_qry_log_tgt_all_hst）的INNERJOIN，这三个表数据量都在百万级别，且都是分区表（以acctstringdate为分区键），执行计划如下：
------------------------------------------------------------------------------------------------------------------------
|Id |Operation |Name |Rows |Bytes|Cost |Pstart|Pstop|
------------------------------------------------------------------------------------------------------------------------
| 0|SELECTSTATEMENT | | 1| 159| 8654| | |
| 1| PXCOORDINATOR | | | | | | |
| 2| PXSENDQC(RANDOM) |:TQ10002 | 1| 159| 8654| | |
| 3| SORTUNIQUE | | 1| 159| 8654| | |
| 4| PXRECEIVE | | 1| 36| 3| | |
| 5| PXSENDHASH |:TQ10001 | 1| 36| 3| | |
|* 6| TABLEACCESSBYLOCALINDEXROWID|DR_QRY_LOG_EXP_HST | 1| 36| 3| | |
| 7| NESTEDLOOPS | | 1| 159| 8633| | |
| 8| NESTEDLOOPS | | 8959| 1076K| 4900| | |
| 9| BUFFERSORT | | | | | | |
| 10| PXRECEIVE | | | | | | |
| 11| PXSENDBROADCAST |:TQ10000 | | | | | |
| 12| PARTITIONRANGEITERATOR | | 1| 56| 4746| KEY| 14|
|*13| TABLEACCESSFULL |META_DR_QRY_LOG_TGT_ALL_HST | 1| 56| 4746| KEY| 14|
| 14| PXBLOCKITERATOR | | 8959| 586K| 154| KEY| KEY|
|*15| TABLEACCESSFULL |META_DBQL_TABLE_USAGE_EXP_HST| 8959| 586K| 154| KEY| KEY|
| 16| PARTITIONRANGEITERATOR | | 1| | 2| KEY| KEY|
|*17| INDEXRANGESCAN |DR_QRY_LOG_EXP_HST_IDX | 1| | 2| KEY| KEY|
------------------------------------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
6-filter("RL"."STATEMENT_ID"="TGT"."STATEMENT_ID"AND"RL"."SYSTEMNAME"="TGT"."SYSTEMNAME"AND"SRC"."SYSTEMNAME"="RL"."SYSTEMNAME")
13-filter(UPPER("TGT"."SYSTEMNAME")="MOZART"ANDUPPER("TGT"."DATABASENAME")="GDW_TABLES"AND
UPPER("TGT"."TABLENAME")="SSA_SLNG_LSTG_MTRC_SD"AND"TGT"."ACCTSTRINGDATE">SYSDATE@!-30AND"TGT"."SYSTEMNAME"ISNOTNULL
"TGT"."DATABASENAME"ISNOTNULLAND"TGT"."TABLENAME"ISNOTNULL)
15-filter("SRC"."ACCTSTRINGDATE"="TGT"."ACCTSTRINGDATE"AND(UPPER("TGT"."SYSTEMNAME")<>UPPER("SRC"."SYSTEMNAME")OR
UPPER("TGT"."DATABASENAME")<>UPPER("SRC"."DATABASENAME")ORUPPER("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)
定位问题
从上面执行计划中的表连接方式可以知道，这三个表之间进行了两次NESTEDLOOP，问题出现在最里层的NESTEDLOOP（对两个表都做了TABLEFULLSCAN），因为表都是百万级别的（即时过滤后的数据量也不小），性能问题就出现在内表（即被驱动表）META_DBQL_TABLE_USAGE_EXP_HST做了太多次的全表扫描。如果能把全表扫描转换成索引，则性能可以大幅度提高。
这种连接的好处是内存使用非常少。
如果驱动数据源有限，且被驱动表在连接列上有相应的索引，则这种连接方式才是高效的。
下面是这三个表上索引的情况：
SQL>selectindex_name,table_namefromuser_indexeswheretable_namein("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
CREATEINDEX"GV"."META_DR_QRY_LOG_TGT_ALL_IDX"ON"GV"."META_DR_QRY_LOG_TGT_ALL_HST"("STATEMENT_ID","ACCTSTRINGDATE")
CREATEINDEX"GV"."META_DBQL_TUSAGE_EHST_IDX"ON"GV"."META_DBQL_TABLE_USAGE_EXP_HST"("QUERYID","ACCTSTRINGDATE")
CREATEINDEX"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上也创建本地分区索引如下：
createindexDR_QRY_LOG_EXP_HST_IDX2ongv.DR_QRY_LOG_EXP_HST(statement_id,ACCTSTRINGDATE)local;
性能对比
新的执行计划如下：
------------------------------------------------------------------------------------------------------------------------
|Id |Operation |Name |Rows |Bytes|Cost |Pstart|Pstop|
------------------------------------------------------------------------------------------------------------------------
| 0|SELECTSTATEMENT | | 1| 159| 4838| | |
| 1| SORTUNIQUE | | 1| 159| 4838| | |
|* 2| TABLEACCESSBYLOCALINDEXROWID |META_DBQL_TABLE_USAGE_EXP_HST| 1| 67| 3| | |
| 3| NESTEDLOOPS | | 1| 159| 4816| | |
| 4| NESTEDLOOPS | | 18| 1656| 4762| | |
| 5| PARTITIONRANGEITERATOR | | 1| 56| 4746| KEY| 14|
|* 6| TABLEACCESSFULL |META_DR_QRY_LOG_TGT_ALL_HST | 1| 56| 4746| KEY| 14|
| 7| PARTITIONRANGEITERATOR | | 18| 648| 16| KEY| 14|
|* 8| TABLEACCESSBYLOCALINDEXROWID|DR_QRY_LOG_EXP_HST | 18| 648| 16| KEY| 14|
|* 9| <STRONG>INDEXRANGESCAN |DR_QRY_LOG_EXP_HST_IDX2</STRONG> | 31| | 15| KEY| 14|
| 10| PARTITIONRANGEITERATOR | | 1| | 2| KEY| KEY|
|*11| INDEXRANGESCAN |META_DBQL_TUSAGE_EHST_IDX | 1| | 2| KEY| KEY|
------------------------------------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
2-filter((UPPER("TGT"."SYSTEMNAME")<>UPPER("SRC"."SYSTEMNAME")OR
UPPER("TGT"."DATABASENAME")<>UPPER("SRC"."DATABASENAME")ORUPPER("TGT"."TABLENAME")<>UPPER("SRC"."TABLENAME"))
AND"SRC"."SYSTEMNAME"="RL"."SYSTEMNAME")
6-filter(UPPER("TGT"."SYSTEMNAME")="MOZART"ANDUPPER("TGT"."DATABASENAME")="GDW_TABLES"AND
UPPER("TGT"."TABLENAME")="SSA_SLNG_LSTG_MTRC_SD"AND"TGT"."ACCTSTRINGDATE">SYSDATE@!-30AND"TGT"."SYSTEMNAME"
ISNOTNULLAND"TGT"."DATABASENAME"ISNOTNULLAND"TGT"."TABLENAME"ISNOTNULL)
8-filter("RL"."SYSTEMNAME"="TGT"."SYSTEMNAME")
9-access("RL"."STATEMENT_ID"="TGT"."STATEMENT_ID"AND"RL"."ACCTSTRINGDATE">SYSDATE@!-30AND
"RL"."ACCTSTRINGDATE"ISNOTNULL)
11-access("SRC"."QUERYID"="RL"."QUERYID"AND"SRC"."ACCTSTRINGDATE"="RL"."ACCTSTRINGDATE")
filter("SRC"."ACCTSTRINGDATE"="TGT"."ACCTSTRINGDATE"AND"SRC"."ACCTSTRINGDATE">SYSDATE@!-30)
从新的的执行计划可以看出，它的第一个NESTEDLOOP果然用了最新创建的索引。
下面是执行时间：
已用时间: 00:00:02.16
两秒种搞定，远远超出他期望的5s：）
相关文章
- 240个Python练习案例附源码(百看不如一练)
- 亚马逊云科技助力海信集团智慧家居全球化运营，解锁全球服务实践案例
- 移动端笔记(8)之bootstrap案例
- 从ObjectPool到CAS指令的案例分享
- 软件7大设计原则（附案例所敲代码）
- 使用Vue3.0实现一个简单的TodoList案例
- 1月安全专项评测报告发布，速看典型案例分析及解决方案！
- 故障案例：MySQL唯一索引有重复值，官方却说This is not a bug
- App逆向案例 X嘟牛 - Frida监听 & WT-JS工具还原（一）
- 互联网电商大厂的分布式事务使用案例
- 【愚公系列】2023年01月 .NET CORE工具案例-DotnetSpider爬虫框架的配置式爬虫
- 技术分享 | MySQL 覆盖索引优化案例一则
- 【Nuxt.js】案例练习入门
- STL案例员工分组
- MySQL 索引优化案例
- MySQL一对一关系的实际应用案例分享（mysql一对一案例）
- Oracle数据库主键索引失效案例分析（oracle主键索引失效）