如何利用分析函数改写范围判断自关联查询详解
前言
最近碰到一个单条SQL运行效率不佳导致数据库整体运行负载较高的问题。
分析、定位数据库的主要负载是这条语句引起的过程相对简单,通过AWR报告就可以比较容易的完成定位,这里就不赘述了。
现在直接看一下这个导致性能问题的SQL语句,其对应的SQLREPORT统计如下:
StatName | StatementTotal | PerExecution | %SnapTotal |
ElapsedTime(ms) | 363,741 | 363,740.78 | 8.42 |
CPUTime(ms) | 362,770 | 362,770.00 | 8.81 |
Executions | 1 | ||
BufferGets | 756 | 756.00 | 0.00 |
DiskReads | 0 | 0.00 | 0.00 |
ParseCalls | 1 | 1.00 | 0.01 |
Rows | 50,825 | 50,825.00 | |
UserI/OWaitTime(ms) | 0 | ||
ClusterWaitTime(ms) | 0 | ||
ApplicationWaitTime(ms) | 0 | ||
ConcurrencyWaitTime(ms) | 0 | ||
Invalidations | 0 | ||
VersionCount | 1 | ||
SharableMem(KB) | 28 |
从SQL的性能指标上看,其单次执行需要6分钟左右,处理5万多条记录,逻辑度只有756,主要消耗时间在CPU上。而这里就存在疑点,逻辑读如此之低,而CPU时间花费又如此之高,那么这些CPU都消耗在哪里呢?当然这个问通过SQL的统计信息中是找不到答案的,我们下面关注SQL的执行计划:
Id | Operation | Name | Rows | Bytes | TempSpc | Cost(%CPU) | Time |
0 | SELECTSTATEMENT | 1226(100) | |||||
1 | SORTORDERBY | 49379 | 3375K | 3888K | 1226(2) | 00:00:05 | |
2 | HASHJOINANTI | 49379 | 3375K | 2272K | 401(3) | 00:00:02 | |
3 | TABLEACCESSFULL | T_NUM | 49379 | 1687K | 88(4) | 00:00:01 | |
4 | TABLEACCESSFULL | T_NUM | 49379 | 1687K | 88(4) | 00:00:01 |
从执行计划看,Oracle选择了HASHJOINANTI,JOIN的两张表都是T_NUM,且都采用了全表扫描,并未选择索引。仅靠执行计划也只等得到上面的结论,至于为什么不选择索引,以及为什么执行时间过长,还需要进一步的分析。
将原SQL进行简单脱密改写后,SQL文本类似如下:
SELECTBEGIN,END,ROWID,LENGTH(BEGIN) FROMT_NUMA WHERENOTEXISTS( SELECT1 FROMT_NUMB WHEREB.BEGIN<=A.BEGIN ANDB.END>=A.END ANDB.ROWID!=A.ROWID ANDLENGTH(B.BEGIN)=LENGTH(A.BEGIN));
如果分析SQL语句,会发现这是一个自关联语句,在BEGIN字段长度相等的前提下,想要找到哪些不存在BEGIN比当前记录BEGIN小且END比当前记录END大的记录。
简单一点说,表中的记录表示的是由BEGIN开始到END截至的范围,那么当前想要获取的结果是找出哪些没有范围所包含的范围。需要注意的是,对于当前的SQL逻辑,如果存在两条范围完全相同的记录,那么最终这两条记录都会被舍弃。
业务的逻辑并不是特别复杂,但是要解决一条记录与其他记录进行比较,多半采用的方法是自关联,而在这个自关联中,既有大于等于又有小于等于,还有不等于,仅有的一个等于的关联条件,来自范围段BEGIN的长度的比较。
显而易见的是,如果是范围段本身的比较,其选择度一般还是不错的,但是如果只是比较其长度,那么无疑容易产生大量的重复,比如在这个例子中:
SQL>selectlength(begin),count(*)fromt_numgroupbylength(begin)orderby2desc; LENGTH(BEGIN)COUNT(*) ————-———- 1222096 119011 138999 148186 1649 945 841 727
大量重复的数据出现在长度为11到14的范围上,在这种情况下,仅有的一个等值判断条件LENGTH(BEGIN)是非常低效的,这时一条记录根据这个等值条件会关联到近万条记录,设置关联到两万多条记录,显然大量的实践消耗在低效的连接过程中。
再来看一下具体的SQL语句,会发现几乎没有办法建立索引,因为LENGTH(BEGIN)的选择度非常查,而其他的条件都是不等查询,选择度也不会好,即使建立索引,强制执行选择索引,效率也不会好。
那么如果想要继续优化这个SQL,就只剩下一个办法,那就是SQL的改写。对于自关联查询而言,最佳的改写方法是利用分析函数,其强大的行级处理能力,可以在一次扫描过程中获得一条记录与其他记录的关系,从而消除了自关联的必要性。
SQL改写结果如下:
SELECTBEGIN,OLDENDEND,LENGTH(BEGIN) FROM( SELECTBEGIN,OLDEND,END,LENGTH(BEGIN),COUNT(*)OVER(PARTITIONBYLENGTH(BEGIN),BEGIN,OLDEND)CN, ROW_NUMBER()OVER(PARTITIONBYLENGTH(BEGIN),ENDORDERBYBEGIN)RN FROM ( SELECTBEGIN,ENDOLDEND,MAX(END)OVER(PARTITIONBYLENGTH(BEGIN)ORDERBYBEGIN,ENDDESC)END FROMT_NUM ) ) WHERERN=1 ANDCN=1;
简单的说,内层的分析函数MAX用来根据BEGIN从小到大,END从大到小的条件,确定每个范围对应的最大的END的值。而外层的两个分析函数,COUNT用来去掉完全重复的记录,而ROW_NUMBER用来获取范围最大的记录(也就是没有被其他记录的范围所涵盖)。
改写后,这个SQL避免对自关联,也就不存在关联条件重复值过高的性能隐患了。在模拟环境中,性能对比如下:
SQL>SELECTBEGIN,END,ROWID,LENGTH(BEGIN) 2FROMT_NUMA 3WHERENOTEXISTS( 4SELECT1 5FROMT_NUMB 6WHEREB.BEGIN<=A.BEGIN 7ANDB.END>=A.END 8ANDB.ROWID!=A.ROWID 9ANDLENGTH(B.BEGIN)=LENGTH(A.BEGIN)) 10; 48344rowsselected. Elapsed:00:00:57.68 ExecutionPlan ———————————————————- Planhashvalue:2540751655 ———————————————————————————— |Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time| ———————————————————————————— |0|SELECTSTATEMENT||48454|1703K||275(1)|00:00:04| |*1|HASHJOINANTI||48454|1703K|1424K|275(1)|00:00:04| |2|TABLEACCESSFULL|T_NUM|48454|851K||68(0)|00:00:01| |3|TABLEACCESSFULL|T_NUM|48454|851K||68(0)|00:00:01| ———————————————————————————— PredicateInformation(identifiedbyoperationid): ————————————————— 1–access(LENGTH(TO_CHAR(“B”.”BEGIN”))=LENGTH(TO_CHAR(“A”.”BEGIN”))) filter(“B”.”BEGIN”<=”A”.”BEGIN”AND“B”.”END”>=”A”.”END”AND “B”.ROWID<>”A”.ROWID) Statistics ———————————————————- 0recursivecalls 0dbblockgets 404consistentgets 0physicalreads 0redosize 2315794bytessentviaSQL*Nettoclient 35966bytesreceivedviaSQL*Netfromclient 3224SQL*Netroundtripsto/fromclient 0sorts(memory) 0sorts(disk) 48344rowsprocessed SQL>SELECTBEGIN,OLDENDEND,LENGTH(BEGIN) 2FROM( 3SELECTBEGIN,OLDEND,END,LENGTH(BEGIN),COUNT(*)OVER(PARTITIONBYLENGTH(BEGIN),BEGIN,OLDEND)CN, 4ROW_NUMBER()OVER(PARTITIONBYLENGTH(BEGIN),ENDORDERBYBEGIN)RN 5FROM 6( 7SELECTBEGIN,ENDOLDEND,MAX(END)OVER(PARTITIONBYLENGTH(BEGIN)ORDERBYBEGIN,ENDDESC)END 8FROMT_NUM 9) 10) 11WHERERN=1 12ANDCN=1; 48344rowsselected. Elapsed:00:00:00.72 ExecutionPlan ———————————————————- Planhashvalue:1546715670 —————————————————————————————— |Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time| —————————————————————————————— |0|SELECTSTATEMENT||48454|2460K||800(1)|00:00:10| |*1|VIEW||48454|2460K||800(1)|00:00:10| |*2|WINDOWSORTPUSHEDRANK||48454|1845K|2480K|800(1)|00:00:10| |3|WINDOWBUFFER||48454|1845K||800(1)|00:00:10| |4|VIEW||48454|1845K||311(1)|00:00:04| |5|WINDOWSORT||48454|662K|1152K|311(1)|00:00:04| |6|TABLEACCESSFULL|T_NUM|48454|662K||68(0)|00:00:01| —————————————————————————————— PredicateInformation(identifiedbyoperationid): ————————————————— 1–filter(“RN”=1AND“CN”=1) 2–filter(ROW_NUMBER()OVER(PARTITIONBYLENGTH(TO_CHAR(“BEGIN”)),”END” ORDERBY“BEGIN”)<=1) Statistics ———————————————————- 0recursivecalls 0dbblockgets 202consistentgets 0physicalreads 0redosize 1493879bytessentviaSQL*Nettoclient 35966bytesreceivedviaSQL*Netfromclient 3224SQL*Netroundtripsto/fromclient 3sorts(memory) 0sorts(disk) 48344rowsprocessed
原SQL运行时间接近1分钟,而改写后的SQL语句只需要0.72秒,执行时间变为原本的1/80,逻辑读减少一半。
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对毛票票的支持。