[Oracle] 一个通过添加本地分区索引提高SQL性能的案例
来源:互联网 发布:wto数据库怎么用 编辑:程序博客网 时间:2024/05/17 06:46
今天接到同事求助,说有一个select query,在Oracle上要跑一分多钟,他希望能在5s内出结果,该sql如下:
Select /*+ parallel(src, 8) */ distinct src.systemname as systemname , src.databasename as databasename , src.tablename as tablename , src.username as usernamefrom meta_dbql_table_usage_exp_hst src inner join DR_QRY_LOG_EXP_HST rl on src.acctstringdate = rl.acctstringdate and src.queryid = rl.queryid And Src.Systemname = Rl.Systemname and src.acctstringdate > sysdate - 30 And Rl.Acctstringdate > Sysdate - 30 inner join meta_dr_qry_log_tgt_all_hst tgt on upper(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.acctstringdate and rl.statement_id = tgt.statement_id 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,这三个表数据量都在百万级别,且都是分区表(以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' 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)
定位问题
从上面执行计划中的表连接方式可以知道,这三个表之间进行了两次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_HSTMETA_DBQL_TUSAGE_EHST_IDX META_DBQL_TABLE_USAGE_EXP_HSTDR_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") 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)
从新的的执行计划可以看出,它的第一个NESTED LOOP果然用了最新创建的索引。
下面是执行时间:
已用时间: 00: 00: 02.16
两秒种搞定,远远超出他期望的5s :)
方法总结
NESTED LOOP高效的条件:驱动数据源有限,且被驱动表在连接列上有相应的索引。
- [Oracle] 一个通过添加本地分区索引提高SQL性能的案例
- Oracle 通过复合索引提高查询性能的一个真实客户例子
- Oracle本地分区索引的使用小结
- Oracle本地分区索引的使用小结
- oracle表的分区本地索引以及全局索引
- 全局索引与本地分区索引的一个重要区别
- 通过 SQL Server 2005 索引视图提高性能
- 通过 SQL Server 2005 索引视图提高性能
- 通过 SQL Server 2005 索引视图提高性能
- Oracle分区键与分区本地索引
- 计算质数通过分区(Partition)提高Spark的运行性能
- Oracle分区表创建本地索引和局部索引的性能
- oracle 函数索引 提高性能
- oracle本地索引分区、全局索引分区…
- Oracle分区、索引sql脚本
- Oracle 的sql优化提高性能
- Oracle 的sql优化提高性能
- oracle :性能优化的一个案例
- 关于使用ssh框架时,pojo、dao、service三层的关系
- 大数阶乘算法
- sizeof用法小结
- 第五部分 家庭创业奔小康13.“声东击西”赚钱术
- QT5无法输入中文,如何解决???
- [Oracle] 一个通过添加本地分区索引提高SQL性能的案例
- 百炼2733:判断闰年
- ZOJ 2281 Way to Freedom
- java解析php函数json_encode unicode 编码问题
- 数组分页导致的性能损失验证程序
- RCP全局缓存
- 对hibernate的set集合进行排序
- easyui datagrid实现批量添加、删除、修改,列多对多动态配比
- ext3.2 form 布局 之radio