解析一个通过添加本地分区索引提高SQL性能的案例

来源：无忧教育
今天接到同事求助，说有一个select query，在Oracle上要跑一分多钟，他希望能在5s内出结果，以下就是解决这个问题的方法，需要的朋友可以参考下该sql如下：代码如下:
Select /*+ parallel(src, 8) */ distinct src.systemname as systemname ,
 


 今天接到同事求助，说有一个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 username

from 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 | | 59 | 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 | | 59 | 586K| 154 | KEY | KEY |

|* 15 | TABLE ACCESS FULL | META_DBQL_TABLE_USAGE_EXP_HST | 59 | 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) 





无忧教育还为您提供以下相关内容希望对您有帮助：

SQL性能调优:最佳实践分享

一、索引优化 索引是提高查询性能的关键工具。以下是一些关于索引优化的建议：为经常用于查询条件的列创建索引：特别是WHERE子句中的列，索引可以显著提高查询速度。避免在列上使用函数或计算：这会导致索引失效，因为索引通常只能用于直接比较列值的查询。定期分析和优化索引：使用数据库提供的工具（如MySQL的ANALYZE TABLE）


如何利用索引提高SQLServer数据处理的效率

1、大多数表都应该有聚簇索引或使用分区来降低对表尾页的竞争,在一个高事务的环境中,对最后一页的封锁严重影响系统的吞吐量。 2、在聚簇索引下,数据在物理上按顺序排在数据页上,重复值也排在一起,因而在那些包含范围检查(between、&lt;、&lt;=、&gt;、&gt;=)或使用group by或order by的查询时,一旦找到具有范围中第一...


SQL 使用order by ,怎样提高排序的性能,数据大约100万条

除了在order by字段上创建索引外，还应该考虑返回的字段。如果只需要返回少量字段，那么可以使用SELECT语句的列名来指定需要返回的字段。这样可以减少返回的数据量，提高查询性能。此外，查询条件也是影响排序性能的重要因素。通过合理使用查询条件，可以缩小数据范围，减少需要排序的数据量。例如，你可以添加WHERE...


分区表需要主键吗 sqlserver

数据分布优化分区键参与主键后，数据按分区键值自动路由至对应分区。如上述案例中，插入OrderDate='2023-01-15'的记录会被定向到1月分区，减少数据倾斜风险。查询性能提升当查询条件包含分区键时（如WHERE OrderDate BETWEEN '2023-01-01' AND '2023-01-31'），SQL Server可仅扫描目标分区，而非全表...


SQLServer索引的性能问题

在良好的数据库设计基础上 能有效地使用索引是SQL Server取得高性能的基础 SQL Server采用基于代价的优化模型 它对每一个提交的有关表的查询 决定是否使用索引或用哪一个索引 因为查询执行的大部分开销是磁盘I/O 使用索引提高性能的一个主要目标是避免全表扫描 因为全表扫描需要从磁盘上读表的每一个数据页 如果...


Oracle分区功能提高应用程序性能

Oracle 分区功能可以提高许多应用程序的可管理性 性能与可用性 通过分区功能 可以将表 索引和索引组织表进一步细分为段 从而能够更精确地管理和访问这些数据库对象 Oracle 提供了种类繁多的分区方案以满足每种业务要求 而且 因为在 SQL 语句中分区是完全透明的 所以该功能几乎可应用于任何应用程序  分区功能的优势  分...


记一交易系统Oracle集群大量等待事件gc buffer busy acquire/release分析...

在Oracle RAC集群中，GC buffer busy acquire/release等待事件主要是由于数据热块导致的，这可以通过优化分区策略、增加初始化表的行数、增加高ITL等待段的inittrans值来解决。以下是详细分析：问题详情：在交易系统压测中，出现了大量GC和缓冲区忙碌等待事件，以及少量的索引争用等待。通过分析Top SQL和Top ...


gaussdb查询缓慢

一、创建索引 对于模糊查询性能慢的问题，可以通过创建索引来提升查询性能。特别是当使用LIKE进行后模糊匹配（如'A123%'）时，可以建立BTREE索引，并根据字段数据类型设置对应的operator，如text类型设置text_pattern_ops，varchar类型设置varchar_pattern_ops等，以优化查询效率。二、优化分区表 分区表查询性能...


怎样保持Oracle数据库SQL性能的稳定性

对于范围和列表分区 可能存在各个分区之间数据量极不均匀的情况下 比如分区表orders按地区area进行了分区 P 分区只有几千行 而P 分区有 万行数据 同时假如有一列product_id 其上有一个本地分区索引 有如下的SQL select * from orders where area=:b and product_id =:b 这条SQL由于有area条件 ...


oracle数据库如何加快索引维护操作

Oracle数据库加快索引维护操作的方法主要包括以下几个方面：监控索引使用情况：通过执行特定的SQL语句来监控索引的使用情况，这是控制索引数量的有效手段。观察一段时间内的索引使用情况，识别出那些不常用的索引。删除这些不常使用的索引，以减少不必要的维护开销。在数据迁移时控制索引：在数据导入导出过程中...
解析一个通过添加本地分区索引提高SQL性能的案例

猜你还关注
